SQL Server Connectors in 2022

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=.....

^{* 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=.....

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.

External references

Image by Jake Parkinson from Pixabay