How to link SQL Server / RDBMS tables into MS Access without a DSN

Linking tables from RDBMS like SQL Server into an Access front end doesn't need an ODBC DSN to be configured, it can be done with a one-liner. I’m just posting this here because I keep having to look it up and can never find it.
This example is for SQL Server; change the driver for other databases. Run this from the immediate window or a macro (adjust server, table names and credentials to suit):

docmd.TransferDatabase acLink, "ODBC","ODBC;DRIVER=SQL Server;SERVER=myServer\myInstanceIfNeeded;DATABASE=myDatabaseName;Trusted_connection=yes;", acTable,"myTableName","myTableName",False,True

The final parameter ensures that login credentials are stored.

Of course our production applications like HBSMR and PACS manage database connections in a more sophisticated manner, with the ability to switch databases and define which tables/views are attached in one operation, but the above technique can be useful where MS Access is being used for quick querying/editing/reporting operations.

Posted by Crispin Flower 30 March 2017 Categories: MS Access SQL Server


Find out more