Creating a System DNS on the fly

[Rico]

Hello,

Can anyone tell me, is there an easy way to 1) Check to see if a DSN already
exists and if not, create a system DSN for the back end?

Also, is there an easy way to use the SQL Server connection dialog should
the user want to set up a server / database that is different than the
default installation?

Any help would be greatly appreciated.

Thanks!
Rick

 

[Guest]

Hi Rico,

The answer is NOT to use a DSN, but to use a connection string generated in
your code. That way you could have the name of the Server, Database, User,
Password etc stored locally in an Access table and generate the connection
string from those.

Hope that helps.

Damian.

 

[Rico]

Hi Damian,

Can I create ODBC links from that Code though? There are some cases where I
will not be using a disconnected recordset and need to use a continuous
form.

Rick

 

[Guest]

Hi again,

Yes, you can... check out the docmd.transferdatabase acLink method. Here's
a sample from google searching:

DoCmd.TransferDatabase acLink, "ODBC", _
"ODBC;DRIVER=SQL Server;" & _
"UID=UserId;" & _
"DATABASE=DBName;" & _
"SERVER=DBServer;" & _
"PWD=Password", acTable, TableName, TableName, False, True

Hope this helps.

Damian.

 

[=?windows-1250?Q?Vladim=EDr_Cvajniga?=]

Hi, Rick,

for MySQL you try something like this:
ODBC;DRIVER={MySQL ODBC 3.51
Driver};SERVER=192.168.1.0;PORT=3306;DATABASE=dbName;UID=userName;PWD=I_wont_tell_you;OPTION=3

Also see http://www.connectionstrings.com/.

HTH

Vlado

 

[Tony Toews [MVP]]

Also, is there an easy way to use the SQL Server connection dialog should
the user want to set up a server / database that is different than the
default installation?

I much prefer DSN-Less connections as it is one less thing for someone
to have to configure and one less thing for the users to screw up.
This is also better for Citrix/TS farms where each individual system
would have to have a DSN created and maintained.

Using DSN-Less Connections
http://www.accessmvp.com/djsteele/DSNLessLinks.html
ODBC DSN-Less Connection Tutorial Part I
http://www.amazecreations.com/datafast/GetFile.aspx?file=ODBCTutor01.htm&Article=true
HOWTO: Use "DSN-Less" ODBC Connections with RDO and DAO
http://support.microsoft.com/?id=147875
ODBC DSN Less
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Rico]

Thanks for the feedback all!

My concern is if a conneciton does not exist, what is the easiest way to
prompt the user or administrator to end ther server name, password etc? Can
the SQL Server connection dialog be used easily enough?

Rick

 

[=?windows-1250?Q?Vladim=EDr_Cvajniga?=]

Either you can use SQL connector dialogue or create your own:
http://img137.imagevenue.com/img.php?image=33335_DSNconnect_122_178lo.jpg
(it's in Czech language but I hope it'll be helpful)

Vlado

 

[Rico]

Thanks Vlad!

Rick

Either you can use SQL connector dialogue or create your own:
http://img137.imagevenue.com/img.php?image=33335_DSNconnect_122_178lo.jpg
(it's in Czech language but I hope it'll be helpful)

Vlado