Detangling the New Microsoft SQL Server ODBC and OLEDB Drivers
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 head-scratcher 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.
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.
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:
ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=myServer;DATABASE=myDatabase;
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:
Provider=MSOLEDBSQL;Server=myServer; Database=myDataBase;
32-bit or 64-bit?
One common question that comes up is whether one should install the 64-bit or 32-bit versions of the driver. The answer is same regardless of which versions we are discussing and it’s always dependent on the OS, not the Office. Therefore, if you are running 32-bit Access on 64-bit Windows, you would want to install 64-bit drivers. This will include the 32-bit components needed for the 32-bit Access to run with.
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:
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
Set tdf = db.CreateTableDef
tdf.Name = “MyRemoteTable”
tdf.SourceTableName = “dbo.MyRemoteTable”
tdf.Connect = “ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=myServer;DATABASE=myDatabase;”
db.TableDefs.Append
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:
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.ConnectionString = “DRIVER=ODBC Driver 17 for SQL Server;SERVER=myServer;DATABASE=myDatabase;”
con.Open
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:
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.ConnectionString = “Provider=MSOLEDBSQL;Server=myServer; Database=myDataBase;”
con.Open
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.
Ben — great summary about untangling the spider web of ODBC vs. Native Client vs. OLE DB drivers that seem get folks hung up at times — including myself!
One question about the “ODBC over OLE DB” comment and jumping through multiple layers — is that a behavior that one would observe by running an ODBC trace? I haven’t tried it out myself, but I was curious if those DLL traversals would be observable via an ODBC trace, running Process Explorer, or some other monitoring utility…
Also, while reading up on the new MSOLEDBSQL provider, I did notice documentation from Microsoft stating that “…if you use the OLE DB Driver for SQL Server (MSOLEDBSQL) instead of SQLOLEDB, you need to make sure to set the DataTypeCompatibility keyword to “80” so that the new data types will map correctly to the ADO data types.” In the code snippet/example of how you would shape the connection string to call the MSOLEDBSQL provider, it doesn’t mention about passing in the DataTypeCompatibility keyword…so I wasn’t sure about it was absolutely necessary for proper mapping of the newer/larger datatypes. I haven’t used the MSOLEDBSQL provider yet, but am curious on how that would go.
>> https://docs.microsoft.com/en-us/sql/connect/oledb/applications/using-ado-with-oledb-driver-for-sql-server?view=sql-server-2017/
..and lastly, while diving into the nitty-gritty of the SQLOLEDB vs SQLNCLnn vs MSOLEDBSQL providers, I happened to notice a comment from your very own Juan Soto on Microsoft’s announcement concerning the new OLE DB Driver release! Small world 🙂
>> https://blogs.msdn.microsoft.com/sqlnativeclient/2018/03/30/released-microsoft-ole-db-driver-for-sql-server/
Thanks for the insightful post!
Kael,
I’m not so sure about observing the changes via ODBC trace. It might be possible to see some difference due to the MSDASQL’s capabilities, but that wouldn’t be a complete picture because of additional translation that may be done at the OLEDB layer, which would be beyond the ODBC trace. That said, I can definitely say that I did observe significant slowdown when using MSDASQL instead of SQLNCLI years ago for certain operations which I attributed to a shortcoming in MSDASQL’s implementation because MSDASQL can only be generic; it can’t optimize fully for particulars of the backend.
Yes, I’ve seen the advice to use `DataTypeCompatibility` but honestly do not use it. I hadn’t a reason or an evidence demonstrating why it would be necessary. If time permits, I might research into this and see if I can find a concrete reason why it must be used because the way it is, using it with or without seems to work so this might be an edge case?
Thanks for reading!
Do you have a reference where I can read some more about that “Microsoft SQL Server ODBC Driver” which is shipped with Windows is deprecated?
Generally, Microsoft removes documentations for products that has reached the end of life, so I would not expect to find online resource especially since that it is targeting SQL Server 2000, which was long ago EOL’d. Your best bet for finding documentation is from installation media for SQL Server 2000 if you have any. I do have to ask – why do you want to use it anyway? Why not upgrade the ODBC driver?
Good overview. I’m really impressed by how well the new OLEDB driver performs across the web (e.g. with Azure back end).