DNS-Less Connections with Microsoft Access - The Easy Way!

When you read about portable applications written in MS Access, there are basically two approaches:

• write a VBA procedure that modifies the connection string of all linked tables in such a way that the user does not need a DSN
• write a VBA procedure that creates the necessary DSN

The funny thing is when you migrate an Access database to SQL server it automatically creates link tables that do not require a DSN!

Well, there is a way to achieve this without writing a little program in VBA:

1. Open Administrative Tools - Data Sources (ODBC) aka Microsoft ODBC Administrator
2. Create a File DSN there with all the connection parameters
3. Go back to MS Access and use the File DSN you just created to link your tables, and voilá, they do not need a DSN!

If you already have linked tables that rely on a DSN, there is an easy way to fix this:

1. follow steps one and two from above
2. Open the Linked Tables Administrator
3. select all tables and check the little box in the lower left corner, press OK
4. when Access asks you for the DSN, just select the File DSN you created above

I am not sure whether this information already exists somewhere; I was not able to find it, I had to stumble upon the solution by accident. Even on Microsoft's pages I only found the programmatic approach.

Hope this helps someone out there!