Go to Top

Detangling the new SQL Server ODBC and OLEDB drivers and what about Native Client?

Some of you may already know that Microsoft backtracked on their planned deprecation of OLEDB and provided a new OLEDB driver. However, it can be a headscratcher to figure what you should be using. When we were using SQL Server Native Client, it was pretty easy — the Native Client had both OLEDB and ODBC shipped in a single DLL file, making for easy installation. All you had to make sure you were using the right version of Native Client.

With SQL Server now available on Linux, it no longer makes sense to distribute Native Client, since Linux in general don’t support OLEDB, which is mainly a Windows-only technology used mainly by Microsoft products. For that reason, Microsoft has not opted to combine both ODBC and OLEDB into a single DLL. If your application contains VBA code that uses both DAO and ADO, then you would need to install two different providers to get the latest feature and supports for both ODBC and OLEDB respectively.

ADVERTISING
SQL Server Hosting

The naming convention can be a bit confusing because many people will loosely refer to various drivers as simply just “ODBC driver” or “OLEDB provider”. So let’s get the names straight. We’ll start with identifying the deprecated versions and then look at the current versions.

Deprecated Versions

By default, all versions of Windows come with two SQL Server data access client libraries pre-installed:

Microsoft OLE DB Provider for SQL Server (also known as SQLOLEDB)
Microsoft SQL Server ODBC Driver (also known as SQLODBC)

It is very important to note that those are DEPRECATED. Those are targeting SQL Server 2000 and lack new features introduced since. Windows will not ship any new drivers or update those via its Windows Update. Going forward, you, the application developer, must provide the drivers of appropriate version to use with your application, rather than relying on the ones provided by Windows. Do NOT use those in your current development.

Current Versions

With that out of way, let’s look at the correct ODBC driver and OLEDB provider we may want to use.

ODBC Driver 17 for SQL Server

At the time of writing, the ODBC Driver 17 for SQL Server is the latest driver and can be downloaded in the provided link. The connection string looks like this:

OLE DB Driver 18 for SQL Server

At the time of writing, the OLEDB driver 18 is the latest driver. Even though the version is one higher, the feature set is equivalent to the ODBC Driver 17 for SQL Server. The connection string looks like this:

Can I use SQL Server Native Client?

Officially, SQL Server Native Client are supported up to SQL Server 2012. However, you can still use it to connect to newer versions of SQL Server. There are several features that are missing from the Native Client. As time proceed, they will become increasingly unsuited for your needs, especially with Azure technology. Though you might be able to continue using it for your existing applications, we encourage that you plan new developments using the separate ODBC and OLEDB drivers and migrate your existing applications when possible. You must migrate when there’s a need to make use of new technologies that are only supported by those newer drivers (examples include Azure Authentication or Always Encrypted feature).

Do I need both?

Only if you do use both DAO and ADO. Generally speaking, all forms and reports and Access queries are always using DAO. The only time you might use ADO is within VBA code. So if you do not use ADO, you can get away with only the ODBC driver and that should suffice for your need. That means when you normally link your tables, you would use code similar to the following:

The syntax used for tdf.Connect works for pass-through querydef or even for DAO.Workspace.OpenDatabase method’s Connect property.

It is legal to open ADO connections using ODBC but the downside is that you end up going through more layers because you’d be using OLEDB provider for ODBC to connect with the ODBC driver 17 for SQL Server. If you still would rather use ODBC anyway, you can use the following code to use ODBC over OLEDB:

It’s better to avoid the extra layer and use the OLEDB directly. You can use this code to get best performance with your ADO code:

Thus, the only time you will actually need and use the new OLEDB driver for SQL Server is when you do have ADO code in your application and want to use the full capability of ADO, which needs to be enabled by the underlying OLEDB driver.

Additional Reference

Roadmap for Microsoft Data Access Technology

One Response to "Detangling the new SQL Server ODBC and OLEDB drivers and what about Native Client?"

  • Steve G
    September 14, 2018 - 6:22 am Reply

    Good overview. I’m really impressed by how well the new OLEDB driver performs across the web (e.g. with Azure back end).

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
close slider
  • This field is for validation purposes and should be left unchanged.