I was playing around with PowerShell and came across a CmdLet that I thought could be interesting to us database people, the Get-OdbcDriver CmdLet.
Like most CmdLets, Get-OdbcDriver is very straightforward to use.
For instance, you need only do (I’m using “| Format-Table” to make the returned data more compact for the purposes of trying to keep this article shorter in overall length, but it isn’t strictly necessary):
Get-OdbcDriver | Format-Table
and then you’ll get returned a complete listing of all the OBDC drivers on the PC, such as
Name Platform Attribute
---- -------- ---------
Driver da Microsoft para arquivos tex... 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Driver do Microsoft Access (*.mdb) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Driver do Microsoft dBase (*.dbf) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Driver do Microsoft Excel(*.xls) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Driver do Microsoft Paradox (*.db ) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Microsoft Access Driver (*.mdb) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Microsoft Access-Treiber (*.mdb) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Microsoft dBase Driver (*.dbf) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Microsoft dBase-Treiber (*.dbf) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Microsoft Excel Driver (*.xls) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Microsoft Excel-Treiber (*.xls) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Microsoft ODBC for Oracle 32-bit {APILevel, FileUsage, Driver, ConnectFunctions...}
Microsoft Paradox Driver (*.db ) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Microsoft Paradox-Treiber (*.db ) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Microsoft Text Driver (*.txt; *.csv) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
Microsoft Text-Treiber (*.txt; *.csv) 32-bit {APILevel, DriverODBCVer, FileUsage, Driver...}
SQL Server 32-bit {APILevel, FileUsage, Driver, ConnectFunctions...}
Microsoft Access Driver (*.mdb, *.accdb) 32-bit {Driver, APILevel, FileExtns, FileUsage...}
Microsoft Excel Driver (*.xls, *.xlsx... 32-bit {Driver, APILevel, FileExtns, FileUsage...}
Microsoft Access Text Driver (*.txt, ... 32-bit {Driver, APILevel, FileExtns, FileUsage...}
ODBC Driver 17 for SQL Server 32-bit {Driver, APILevel, FileUsage, Setup...}
SQL Server 64-bit {APILevel, FileUsage, Driver, ConnectFunctions...}
ODBC Driver 17 for SQL Server 64-bit {Driver, APILevel, FileUsage, Setup...}
Note that the functions provided below rely on my PS_GetOutput() function which is part of my post VBA – Run PowerShell Command.
Determine SQL Server OBDC Drivers
Knowing the above, I wanted to refine that PowerShell command to restrict the results just to getting a list of SQL Server ODBC driver and I ended up with:
Get-OdbcDriver | Where-Object { $_.Name -Like '*SQL*' } | Sort-Object Name, Platform | Select-Object @{name='Driver'; expression={$_.Name + ' (' + $_.Platform + ')'}}
With that in hand, I then created a VBA wrapper function. The end result was:
'---------------------------------------------------------------------------------------
' Procedure : PS_GetSQLServerODBCDrivers
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Get a list of SQL Server OBDC Drivers
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' References: Requires PS_GetOutput()
'
' Usage:
' ~~~~~~
' ? PS_GetSQLServerODBCDrivers
' Returns -> ODBC Driver 17 for SQL Server (32-bit)~~ODBC Driver 17 for SQL Server (64-bit)~~SQL Server (32-bit)~~SQL Server (64-bit)
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2021-11-12 Initial Public Release
'---------------------------------------------------------------------------------------
Function PS_GetSQLServerODBCDrivers() As String
Dim sPSCmd As String
On Error GoTo Error_Handler
sPSCmd = "(Get-OdbcDriver | Where-Object { $_.Name -Like '*SQL Server*' } " & _
"| Sort-Object Name, Platform " & _
"| Select-Object @{name='Driver'; expression={$_.Name + ' (' + $_.Platform + ')'}} " & _
"| Select-Object -ExpandProperty Driver) -join '~~'"
PS_GetSQLServerODBCDrivers = PS_GetOutput(sPSCmd)
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: PS_GetSQLServerODBCDrivers" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
which you can then use by doing something like
Function Test_OBDCDrivers()
Dim sDrivers As String
Dim aDrivers() As String
Dim vDriver As Variant
sDrivers = PS_GetSQLServerODBCDrivers()
aDrivers = Split(sDrivers, "~~")
For Each vDriver In aDrivers
Debug.Print vDriver
Next vDriver
End Function
which in turn outputs something similar to
ODBC Driver 17 for SQL Server (32-bit) ODBC Driver 17 for SQL Server (64-bit) SQL Server (32-bit) SQL Server (64-bit)
Determine SQL Server OBDC Drivers of Matching Bitness
As most developers are well aware, we must match the bitness of our application with the bitness of the ODBC drivers, so I wanted to further tweak the above to only return those drivers that matched the running databases bitness, without any user interaction (user often have no clue about bitness and I wanted this to be seamless). Being aware of my article VBA – Determine MS Office/Application Bitness I created:
'---------------------------------------------------------------------------------------
' Procedure : PS_GetSQLServerODBCDrivers_MatchBitness
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Get a list of SQL Server OBDC Drivers but that match the Access application
' bitness
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' References: Requires PS_GetOutput()
'
' Usage:
' ~~~~~~
' ? PS_GetSQLServerODBCDrivers_MatchBitness
' Returns -> ODBC Driver 17 for SQL Server (32-bit)~~SQL Server (32-bit)
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2021-11-12 Initial Public Release
'---------------------------------------------------------------------------------------
Function PS_GetSQLServerODBCDrivers_MatchBitness() As String
Dim sPSCmd As String
Dim sBitness As String
On Error GoTo Error_Handler
'Determine the bitness of Access
Select Case Mid(Application.ProductCode, 21, 1)
Case 0
sBitness = "32-bit"
Case 1
sBitness = "64-bit"
End Select
'Retrieve the list of SQL OBDC Driver that match Access' bitness only
sPSCmd = "(Get-OdbcDriver | Where-Object { $_.Name -Like '*SQL Server*' -and $_.Platform -eq '" & sBitness & "' } " & _
"| Sort-Object Name, Platform " & _
"| Select-Object @{name='Driver'; expression={$_.Name + ' (' + $_.Platform + ')'}} " & _
"| Select-Object -ExpandProperty Driver) -join '~~'"
PS_GetSQLServerODBCDrivers_MatchBitness = PS_GetOutput(sPSCmd)
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: PS_GetSQLServerODBCDrivers_MatchBitness" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
which you can then use by doing something like
Function Test_SQLServeerOBDCDrivers()
Dim sDrivers As String
Dim aDrivers() As String
Dim vDriver As Variant
sDrivers = PS_GetSQLServerODBCDrivers_MatchBitness()
aDrivers = Split(sDrivers, "~~")
For Each vDriver In aDrivers
Debug.Print vDriver
Next vDriver
End Function
which in turn outputs something similar to
ODBC Driver 17 for SQL Server (32-bit) SQL Server (32-bit)
So now, we can very easily assertain the OBDC Drivers on a system that work with our database.
In many forum discussions about weird behaviors, one of the first things we ask to check is what version of OBDC drivers are being used and to update them if newer ones are available. This enables us to easily know if the user needs to update their drivers.
What about DSN Drivers?
Once again, PowerShell has a CmdLet for that as well, but that will be for another day.