Code Library

GetLatestOledbProvider(): Use VBA to get the Latest SQL Server OLE DB Provider

This simple VBA function will check the user's registry and return the latest OLE DB provider from a list of supported providers that you can customize.

Mike Wolfe

Dec 14, 2022 • 5 min read

NOTE: This article is about finding OLE DB providers for use in ADO connection strings. For information about finding ODBC drivers for use in ODBC connection strings, check out GetLatestOdbcDriver.

The GetLatestOledbProvider function searches through the registry and returns the latest SQL Server OLE DB Provider for use in ADO connection strings.

A Brief History of SQL Server OLE DB Providers

The original SQL Server OLE DB provider, SQLOLEDB, still ships with Windows so it should be available on any machine running Microsoft Access. Newer versions were released for SQL Server versions 2005 (SQLNCLI), 2008 (SQLNCLI10), and 2012 (SQLNCLI11).

With the release of SQL Server 2014, Microsoft deprecated OLE DB support for SQL Server.

However, Microsoft pledged to continue supporting SQLNCLI11 until SQL Server 2012 went out of support. Before that happened, though, Microsoft undeprecated OLE DB support for SQL Server, releasing MSOLEDBSQL in concert with SQL Server 2017.

The latest OLE DB provider for SQL Server (as of this writing) is MSOLEDBSQL19. The key difference between it and MSOLEDBSQL is that connections are now encrypted by default.

The Approach

The function iterates through an array of triplets:

Provider Names: as used in ADO connection strings (e.g., Provider=MSOLEDBSQL;)
Provider Descriptions: as seen in the "Apps & features" Settings page
Provider GUID: globally unique identifier associated with the provider

The function uses the Provider GUID to check the registry and see if the provider is installed. As soon as it finds a match, it returns the name of the provider for use in an ADO connection string. The provider descriptions are not used in the function, but are included for readability and debugging purposes.

The composition of your list will change over time as OLE DB providers come and go. It may even vary from one Access application to another.

If you don't want to support providers that Microsoft no longer supports, you can leave them off the list. If your application needs some feature that was introduced with a newer version of the provider, you can leave older ones off the list. If you don't want to include new driver versions until you've had a chance to fully test them, you can leave them off the list, too.

Background

Adding support for new OLE DB providers is not as straightforward as for ODBC drivers. For one thing, they are not all grouped inside a single registry key. And, for another, you need to know the provider's GUID.

My GetLatestOledbProvider function uses the provider's GUID to perform a highly efficient registry lookup. Similar functions on the internet iterate through the registry looking for providers, which is relatively slow and inefficient. The downside to my approach is that you need to figure out what the GUIDs are that correspond to the OLE DB providers beforehand.

I got you started by populating the list with all currently supported SQL Server OLE DB providers along with the default provider that ships with Windows.

Supporting New Versions

Depending on when you are reading this, Microsoft may have released newer OLE DB providers than what I've included in my hardcoded list. The easiest way to identify the GUID for a new provider it is to install the provider and then search for it in the registry. Here are the step-by-step instructions.

OLEDB drivers can be found in the registry by:

Opening regedit.exe
Navigating to HKEY_CLASSES_ROOT\CLSID
Going to Edit > Find
Find what: OLEDB_SERVICES
Look at: [√] Values
[Find Next]
Repeatedly pressing [F3] on the keyboard to find additional matches

Here's the entry for the latest SQL Server OLE DB provider, Microsoft OLE DB Driver 19 for SQL Server.

The Function

In my original function, I use my Throw function to raise an error rather than simply displaying a MsgBox. You should customize the last part of the function to suit your own needs.

' ----------------------------------------------------------------
' Procedure : GetLatestOledbProvider
' Date      : 12/14/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/getlatestoledbprovider/
' Purpose   : Iterates through a custom list of OLEDB providers
'               and returns the first installed match.
' ----------------------------------------------------------------
Public Function GetLatestOledbProvider() As String
    Dim SupportedProviders As Variant
    SupportedProviders = Array( _
        "MSOLEDBSQL19", "Microsoft OLE DB Driver 19 for SQL Server", "EE5DE99A-4453-4C96-861C-F8832A7F59FE", _
        "MSOLEDBSQL", "Microsoft OLE DB Driver for SQL Server", "5A23DE84-1D7B-4A16-8DED-B29C09CB648D", _
        "SQLNCLI11", "SQL Server Native Client 11.0", "397C2819-8272-4532-AD3A-FB5E43BEAA39", _
        "SQLNCLI10", "SQL Server Native Client 10.0", "8F4A6B68-4F36-4e3c-BE81-BC7CA4E9C45C", _
        "SQLOLEDB", "Microsoft OLE DB Provider for SQL Server", "0C7FF16C-38E3-11d0-97AB-00C04FC2AD98")
    
    'We're using the registry to check for providers, so we'll just create the registry object
    '   once and use it each time through the loop below
    Dim oReg As Object
    Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    Const HKEY_CLASSES_ROOT = &H80000000
    
    Dim i As Long
    For i = 0 To UBound(SupportedProviders) Step 3
        Dim ProviderName As String: ProviderName = SupportedProviders(i)
        Dim ProviderDesc As String: ProviderDesc = SupportedProviders(i + 1)
        Dim ProviderUID As String: ProviderUID = SupportedProviders(i + 2)
        
        Dim ProviderIsInstalled As Boolean, SubkeyPath As String
        SubkeyPath = "CLSID\{" & ProviderUID & "}\"
        ProviderIsInstalled = (oReg.EnumKey(HKEY_CLASSES_ROOT, SubkeyPath, "", "") = 0)
        
        If ProviderIsInstalled Then
            'Return the first provider from the list that is installed on the computer
            GetLatestOledbProvider = ProviderName
            Set oReg = Nothing
            Exit Function
        End If
    Next i
    Set oReg = Nothing
    
    'If we get here it means there were no matches found
    MsgBox "No OLE DB providers found (not even the default that ships with Windows!)"
End Function