Ms-access – Adding linked table to Access 2003 while keeping ODBC connection info in MDB

dsnms-accessodbc

I have an Access 2003 database MDB where all of the tables exist as linked tables within SQL Server 2005. The MDB file contains all of the ODBC information that points to the correct SQL Server and log-on credentials (trusted connection).

What I would like to do is add a new linked table to the MDB file however I am not sure how to go about specifying the ODBC connection information. When I try to add a new linked table I keep getting prompted to locate or create a DSN file. I don't want to have to create a new DSN entry on every machine, rather I would like all that information stored within the Access MDB file itself.

In the existing database I can "hover" over the table names and see the ODBC connection info as a tool-tip. All I need to do is add another linked table using the same connection information.

I do have access to the SQL Server where the tables are linked to,. I have already created the new table I wanted to add. I just need to find a way to link to it.

Best Solution

For what it's worth, I'm lazy. I keep a DSN on my development machine, and use it to create new linked tables. I then run Doug Steele's code to convert the links to dsnless connections before distribution of the front end to the end users.

Related Question