One great thing about Access is all the various ways it can connect to data in tables. Using ODBC, Access can connect to anything with an ODBC driver that you can install.

I’ve done connections using datasource names as well as DSN-less connections to SQL Server.

A datasource name is something that you set up in the Windows ODBC Admin through the control panel. It allows you to configure and use an ODBC driver that is installed on your system. Then in Access you just provide the data source name (DSN) and link tables available in that datasource.

A DSN-less connection is actually just the ability of Access to use another ODBC connection method that uses a Connect String. Each ODBC driver accepts a string specifying various parameters in order to connect to a particular server or database using that driver.

Either way, you need to have a driver installed on the system that Access is using. There are a handful of drivers that are pre-installed with Windows, and additional drivers installed with Office and other programs. Generally, you can find standalone drivers for a particular database by entering the name of the database and ODBC driver into your favorite search engine. Typically there is an executable file you can download to install it.

Using a standard driver included with Windows or Office products is really only desirable if you have no other option. The SQL Server ODBC driver that comes standard with Windows for example is not updated regularly and will not be as efficient or utilize the latest options. The benefit though is that if you are distributing an application to others and they cannot install ODBC drivers, there are options. SQL Server is really the only driver of significance pre-installed, but you will also find drivers for Access and Excel that get installed. Generally, you will want to try to connect to those sources for data using other methods in Access, like linking tables and the Access import process.

The only option for MySQL server and Azure SQL for example are to install drivers.

I have been working with other databases and ODBC drivers for much of my multi-decade Access career. Generally, I try to use the most recent version of an ODBC driver and work with customers to get them installed on their systems as needed. When this was difficult or impossible, I have used the default SQL Server driver at times.

Any time you are considering using different backend databases or changing to a new backend database, it is important to realize that interchanging backend databases is not a simple process. Typically each backend database has it’s own idiosyncrasies and challenges. Often queries have to be examined and rewritten to optimize them differently for each database. In addition, there are two types of queries you can write when you are connecting to a different backend.

When you connect through an ODBC driver you can use the DAO (Data Access Object) model which allows you to use Access SQL and the driver translates it into SQL that is native to the database you are connecting to. So one option is the DAO query which is familiar and keeps you in the Access world of query writing.

Another option is to use a Passthrough query. In this type of query you are passing a SQL statement directly to the ODBC driver so it doesn’t have to do any translation. This can greatly improve performance, but is going to require rewrites to the SQL if you choose to change databases later.

In all, this subject is very expansive and we have just touched the surface, but I hope you have been able to learn something new. Thanks for reading!