GetLatestOdbcDriver(): Use VBA to get the Latest SQL Server ODBC Driver

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

The following function searches through the registry and returns the latest SQL Server ODBC Driver.

The function determines the "latest" driver based on a list that you provide.  I consider this a feature, since it means you can provide your own list of supported drivers.  

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

If you don't want to support drivers 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 driver, 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.

The Approach

The function iterates through an array of driver names and checks the registry to see if the driver is installed.  As soon as it finds a match, it returns the name of the driver.  The ODBC drivers are listed in the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers

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 : GetLatestOdbcDriver
' Date      : 12/12/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/getlatestodbcdriver/
' Purpose   : Iterates through a custom list of ODBC drivers
'               and returns the first installed match.
' ----------------------------------------------------------------
Public Function GetLatestOdbcDriver() As String
    
    'Customize this list to include the drivers you want to support
    Dim SupportedDrivers As Variant
    SupportedDrivers = Array( _
        "ODBC Driver 17 for SQL Server", _
        "ODBC Driver 13 for SQL Server", _
        "SQL Server Native Client 11.0", _
        "SQL Server")   'the "SQL Server" driver is the legacy driver that is included with Windows
    
    'We're using the registry to check for Drivers, 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 RegPath As String = "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers"
    Const HKEY_LOCAL_MACHINE = &H80000002
    
    
    Dim i As Long
    For i = 0 To UBound(SupportedDrivers)
        Dim DriverName As String: DriverName = SupportedDrivers(i)
        
        Dim KeyExists As Boolean, RegValue As String
        KeyExists = (oReg.GetStringValue(HKEY_LOCAL_MACHINE, RegPath, DriverName, RegValue) = 0)
        
        Dim DriverIsInstalled
        DriverIsInstalled = (KeyExists And (RegValue = "Installed"))
        
        If DriverIsInstalled Then
            'Return the first Driver from the list that is installed on the computer
            GetLatestOdbcDriver = DriverName
            Set oReg = Nothing
            Exit Function
        End If
    Next i
    Set oReg = Nothing
    
    'If we get here it means there were no matches found;
    '   until we work out all the kinks, we may be better served by returning the default Driver
    '   here rather than throwing an error
    MsgBox "No ODBC Drivers found (not even the default that ships with Windows!)"
End Function

Cover image created with Microsoft Designer

UPDATE [2022-12-15]: Add header note in italics with link to GetLatestOledbProvider() function.