DSN-Less Connections in Access

Since the beginning, Microsoft Access has been able to link to server-based databases with Open Database Connectivity (ODBC).  ODBC is a set of drivers that allow applications to connect to various data sources in a standard way.  Traditionally, the connection information (which driver to use, the server name and database name) were stored in an ODBC-DSN.  A DSN could be either a file on the user’s machine, or a configuration created through the ODBC Control Panel.  In either case, this meant that installing an Access database application that linked to server tables involved making these settings on the user’s machine.  Although there is a way to automate creating the DSN settings, they continue to be a source of problems for users and IT personnel.

Starting with Access 2002, you can make DSN-less connections to server tables.  This means that all the connection information is now stored in the connection string for each table, making the DSN unnecessary.  This is a great improvement for Access developers and can easily remove one installation/maintenance issue.

I have deployed an Access front end application with DSN-less connections and it works great.  There is only one real problem; the Access application interface does not support creating and configuring DSN-Less connections.  When you attempt to link a new table in Access, the GUI leads you to the ODBC dialog box where you have to choose a DSN.  In order to use DSN-less connections you will have to create or modify the link in VBA code.

When you link a table in Access, the corresponding TableDef object contains an ODBC connection string that provides the information necessary to connect to the server and the table.  The connection string for a DSN-Less connection looks like this:

ODBC;DRIVER=SQL Server;SERVER=sqldev;DATABASE=Inventory;Trusted_Connection=Yes

This is a sample that connects to SQL Server and uses integrated security.  Instead of the last part of the string (Trusted_Connection=Yes) you might see a user name and password. 

The VBA code to change the connection string for an existing table looks like this:

Dim db As Database
Dim T As TableDef

Set db = CurrentDb
Set T = db.TableDefs("Inventory")

T.Connect = "ODBC;DRIVER=SQL Server;SERVER=sqldev;DATABASE=Inventory;Trusted_Connection=Yes"
T.RefreshLink

This code sets the Connect property of the existing TableDef and then calls the RefreshLink method to refresh the table.  You can use this code as a basis for a generic procedure to relink all of your tables.

The application I developed has to exist in different environments (Dev., Test, Production) with different database servers.  So I created a local table that contains all the table names and connection strings for each environment.  Then I created a form that allows the user to select the correct environment, click on a button, and the code will refresh the links for each linked table.  Now when a version of the application is deployed, that form is used once to get the links refreshed.  And no action is necessary on the user’s machines.  If there is a new user of the application, they can simply start using it.

I keep the old DSN around on my development computer.  In the rare case that a new table is linked to the application, I can just link it through the normal Access GUI using the DSN, then add entries to my links tables and use the form to refresh the links, causing the new table to have a DSN-less connection string.

My reference for this is the Microsoft Knowledge Base article 892490.