MS-Access Add Additional Linked Tables

Question

I have an MS-Access database that was converted to use SQL Tables using the "Microsoft SQL Server Migration Assistant 2008 for Access" (aka SSMA) and created linked tables (so the MS-Access interface still works but is linked to SQL Tables).

Modifying those tables has been no problem (modify in SQL, use the Linked Table Manager in MS-Access, update tables). But I've not added a new table in SQL and I can't find a way to add that table to the set of linked tables.

I've tried the External Data -> ODBC Database, but it wants me to make a FileDSN and since the SSMA tool didn't require that I don't want to have some tables linked one way and other files liked another way (does anyone know how SSMA links the tables?).

So my underlying question is: Without using DSN how do I add an additional SQL Server table to MS-Access as a linked table?

Answer 1

This is a pretty popular question on SQLServerPedia, and read through the answers here:

http://sqlserverpedia.com/blog/uncategorized/how-can-i-link-a-sql-server-database-to-ms-access-using-link-tables-in-ms-access/

Answer 2

I always create a DSN for development in order to make creating linked tables easy. Then after linking, I run Doug Steele's code to convert them to DSN-less connect strings:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

Theoretically, his code would tell you enough to figure out how to link DSN-less without using the DSN as a starting point, but I've never found it worth the effort, since it's so easy to create the development DSN and run the conversion once the links are created.

--
David W. Fenton
David Fenton Associates