Setting up a File DSN?

[FancyPrairie]

I've always connected to SQL database via File DSN's. However, if I open MSysObjects it shows the connection information rather than the name of the File DSN. But I do have one File DSN that MSysObjects does list the name of the DSN rather than the connection information.

How do I reference a File DSN so that MSysObjects stores the name of the File DSN and not the connection information?

The File DSN that works like I want was created by someone else and its name is ABC.cdd.dsn This is what it looks like (obviously not connecting to SQL):

[ODBC]
DRIVER=CONNX32
UID=MyID
APPLICATION=
NODE=
DD=\\path\SMSPRD.cdd
ACC=CONNX32

The File DSN that doesn't work is named ABC.dsn This is what it looks like:

[ODBC]
DRIVER=SQL Server
UID=MyID
Address=\\MyServerName\pipe\MSSQL$Intranet\sql\query
DATABASE=MyDatabaseName
SERVER=MyServerName
Description=MyStuff

 

[dhookom]

I just created a file DSN (I typically don't use these) and found the connection property of:
[tt]
Description=SAM EC File DSN;DRIVER=SQL Server;SERVER=MyServerName;UID=myreader;PWD=myreader;APP=Microsoft® Windows® Operating System;DATABASE=myDB[/tt]

I then changed the file DSN to point to a different SQL Server but nothing changed in Access. My linked table was still connected to my first server table and the connection properties were exactly the same.

I then used the linked table manager and refreshed the link. It stayed with the first server rather than going back to the DSN file and refreshing with the new server name.

Apparently using a file DSN to link to a table results in something like a DSN-less connection.

Duane
Hook'D on Access
MS Access MVP

 

[FancyPrairie]

Thanks for taking the time to check it out. But it's strange that my first example works like I would expect (that is, storing the name of the DSN file in MSysObjects). I'm not sure I see a difference between my 2 examples that would produce 2 different results.

 

[dhookom]

I expect different drivers use different methods.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

The other thought... you may have created a system DSN on accident that one time. One stupid screen steal from IE or outlook and a click back on the 'application' but really the system tab would do it... Worst inconsistent and unreliable feature ever... Applications stealing the active status.

 

[Hap007]

Hi FancyPrairie,

Here is a link to a good description of DSN:

http://www.truthsolutions.com/sql/odbc/creating_a_new_odbc_dsn.htm

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121