Creating Linked Table Without A DSN

[Matt Delfs]

Hello,

I have a couple of Access databases that contain several linked tables
referring to tables on a SQL server. These linked tables appear to have
"DSNless" connections which is exactly how I want them. The problem is that
I need to add a few more linked tables in this manner and I seem to have
forgotten how I created them. I converted one of these files to Access 2003
which makes viewing the connection string a bit simpler. What I discovered
was that the "DSNless" connections had the following properties:

ODBC;Description=My Connection Description;DRIVER=SQL
Server;SERVER=MyServer;APP=Microsoft Open Database
Connectivity;WSID=MyWSID;DATABASE=MyDatabase;Trusted_Connection=Yes;
TABLE=MyTable

The linked tables that used a DSN have these properties:

ODBC;DSN=MyDSN;Description=My DSN Connection Description;APP=Microsoft
Access;WSID=myWSID;DATABASE=myDatabase;Trusted_Connection=Yes;TABLE=MyTable

To me, the key difference appears to be that the DSN connection DOES NOT
provide a driver or server definition since they are included within the DSN
that it DOES contain. Does anyone have any idea how I created the original
"DSNless" connections? I suspect that I used the Access GUI to create them,
but I just can't remember how.

Thanks,
Matt

 

[Douglas J. Steele]

Actually, you can't do it through the GUI.

See http://members.rogers.com/douglas.j.steele/DSNLessLinks.html for one
approach.

 

[Matt Delfs]

Douglas,

Thanks for the reply. Your code is quite useful. However, between the time
I posted my first message and the time you replied I found an alternative
GUI solution that appears to work. It's not completely DSNless but it does
the trick. Basically, I used a file DSN on my development box that set up
the connection properties and it seems to have created the linked tables by
embedding the connection information in the Access file. Users who open
this Access file do not seem to require this file DSN on their workstations
or network shares. Any thoughts about the possible shortcomings of this
method?

Thanks for your help.
Matt

 

[Douglas J. Steele]

Pretty much impossible for me to comment without seeing the code!