Hey, Scripting Guy! How can I get a list of the ODBC drivers that are installed on a computer?

— VV

Hey, VV. This is one of those questions that end up driving us crazy. That’s because it: a) seems so simple and, b) appears as though there are a number of ways to get this information. For example, on our Windows XP computer we have no fewer than eight WMI classes than include the acronymn ODBC somewhere in the class name. Surely one of those classes will return a list of installed drivers, right?

Wrong. What we’re trying to get at is the information found on the Drivers tab of the ODBC Data Source Administrator dialog box:

But none of the WMI classes seem to return that information, at least not directly. (There might be a convoluted way of getting at this information, but we Scripting Guys try to avoid convoluted ways whenever possible.) All hope seemed lost, but then one of the Scripting Guys decided to apply the Upstairs Closet theory to the problem. In this particular Scripting Home, any time something is lost you can almost bet that it will be found in the upstairs closet, even though the item should have never been thrown into the closet and even though everyone in the house solemnly swears that they didn’t put it there.

The registry, as you doubtless know, is the upstairs closet of the operating system: if you’re willing to look, you can find almost anything in the registry. And, sure enough, that’s where we found the list of installed ODBC drivers, in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers. (Oddly enough, we also found a pair of shoes, that jacket that we always liked, and a hacksaw that disappeared 5 or 6 years ago.)

As soon as we knew we could get this information out of the registry it was easy to write a script that retrieved a list of all the installed ODBC drivers:

Const HKEY_LOCAL_MACHINE = &H80000002

strComputer = “.”
Set objRegistry = GetObject(“winmgmts:\\” & strComputer & “\root\default:StdRegProv”)
strKeyPath = “SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers”
objRegistry.EnumValues HKEY_LOCAL_MACHINE, strKeyPath, arrValueNames, arrValueTypes
For i = 0 to UBound(arrValueNames)
    strValueName = arrValueNames(i)
    objRegistry.GetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue    
    Wscript.Echo arrValueNames(i) & ” — ” & strValue
Next

We begin by defining a constant named HKEY_LOCAL_MACHINE and setting the value to &H80000002; we’ll use this constant to indicate that we want to work with the HKEY_LOCAL_MACHINE registry hive. We connect to the WMI service and to the standard registry provider (root\default:StdRegProv), then use this line of code to assign the registry path within HKEY_LOCAL_MACHINE to the variable strKeyPath:

strKeyPath = “SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers”

As it turns out, the installed ODBC drivers are stored as individual registry values within this registry key, something like this:

Therefore, to retrieve a collection of all these registry values we need to call the EnumValues method, a method which automatically grabs all the values within a specified key:

objRegistry.EnumValues HKEY_LOCAL_MACHINE, strKeyPath, arrValueNames, arrValueTypes

When we call EnumValues we need to supply two in parameters and two out parameters. “In parameters” are values we supply to the method; in this script we pass the constant HKEY_LOCAL_MACHINE and the variable strKeyPath. Taken together, these parameters tell the script which registry key we’re working with.

“Out parameters” represent information that the method provides to us. To get this information all we have to do is provide a pair of variable names. In our script, the variable arrValueNames will end up holding an array of all the registry value names found in SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers; the variable arrValueTypes will hold an array of data types for each of these registry values. (We’re including this particular parameter only because it’s required. Because all our registry values have the same data type – REG_SZ – we don’t need to worry about data types and thus won’t use arrValueTypes.)

After calling EnumValues all the individual registry value names will be stored (as an array) in the variable arrValueNames. To access those values we need to set up a For-Next loop that runs from 0 to the last item (the upper bound or UBound) in the array. (As you know, in VBScript the first item in an array is always item 0.) That’s what this line of code does:

For i = 0 to UBound(arrValueNames)

Inside the loop, we assign the name of the first registry value to a variable named strValueName:

strValueName = arrValueNames(i)

We do that because, at the moment, all we have are the names of the individual registry values. To actually get the values that have been assigned to those registry entries we need to call the GetStringValue method, something we do here:

objRegistry.GetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

As you can see, we pass GetStringValue four parameters:

After that we can simply use this line of code to echo back the name of the registry value and the value assigned to it:

Wscript.Echo arrValueNames(i) & ” — ” & strValue

And when we run the script, we’ll get back output similar to this:

SQL Server — Installed
Microsoft Access Driver (*.mdb) — Installed
Microsoft Text Driver (*.txt; *.csv) — Installed
Microsoft Excel Driver (*.xls) — Installed
Microsoft dBase Driver (*.dbf) — Installed
Microsoft Paradox Driver (*.db ) – Installed

Might not have been as straightforward as we would have liked but you know what they say: a list of installed ODBC drivers is a list of installed ODBC drivers. (We’re not sure why they say that, but at least in this case it makes some sense.)