SQL Server

SQL Server Connectors in 2022

To get access to the latest features--especially security features--be sure you're using the latest ADO provider and ODBC driver for SQL Server.

Mike Wolfe

Feb 19, 2022 • 3 min read

There are two primary kinds of SQL Server "connectors" in Access: ODBC drivers and OLE DB providers.

ODBC drivers are used for DAO connections, linked tables, and pass-through query definitions.

OLE DB providers are used for ADO connections.

Unfortunately, the names used for the different versions of ODBC and OLE DB SQL Server connectors are ambiguous, to say the least. Since I can never remember which one is which–is it MSOLEDBSQL or SQLOLEDB?–I'm writing this article as much for my own reference as for yours.

ODBC Driver for SQL Server

The latest ODBC driver for SQL Server is ODBC Driver 18 for SQL Server.

It's available for download here: https://aka.ms/downloadmsodbcsql.

You refer to it in ODBC connection strings as follows:

ODBC;Driver={ODBC Driver 18 for SQL Server};Server=.....

The start of a typical ODBC connection string for the latest (as of writing) SQL Server ODBC driver.

^{* NOTE: While writing this article, I discovered that ODBC Driver 18 for SQL Server was recently released. I have not used this version myself in production. I have been using ODBC Driver 17 for SQL Server for over a year now without any problems. I don't expect there will be any issues with this newest version, but I can't confirm that from personal experience. Your mileage may vary.}

OLE DB Provider for SQL Server

The latest OLE DB provider for SQL Server is Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL).*

^{* Not to be confused with the available-in-Windows-by-default-but-no-longer-supported Microsoft OLE DB Provider for SQL Server (SQLOLEDB).}

It's available for download here: https://aka.ms/downloadmsoledbsql.

You refer to it in ADO connection strings as follows:

Provider=MSOLEDBSQL;Server=.....

The start of a typical ADO connection string for the latest (as of writing) SQL Server OLE DB provider.

Match Bitness with Windows–Not Office

Both the ODBC driver and OLE DB provider come in 32-bit (x86) and 64-bit (x64) flavors.

Since these connectors are not added via the Tools > References... menu in VBA, you should install the bitness flavor that matches Windows, not VBA. In most cases, then, that means you will want the 64-bit version.

If you use these connectors, keep in mind that you will need to do something to ensure they are installed and available on your end users' computers.