With VBA, find the version of the MySQL ODBC driver installed in Windows

Using Visual Basic for Applications, how can I find out which version of the MySQL ODBC driver is installed in Windows on a user's machine?

I have a Microsoft Access application that uses the MySQL ODBC driver to make a connection. The connection string looks like this:

ODBC;D开发者_运维知识库ATABASE=mydatabase;DRIVER={MySQL ODBC 3.51 Driver};
    OPTION=3;PWD=password;PORT=3306;SERVER=server-db;UID=db-user;

This was working find until the IT manager installed version 5.1 of the MySQL ODBC driver on a user's PC, which broke my connection string.

If I knew the version of the driver installed on the user's Windows XP installation, I could insert that into the connection string at run-time. How can I find out which version of the MySQL ODBC driver is installed in Windows on a user's machine using VBA?

---

You can find it in the registry under

HKEY_LOCAL_MACHINE\SOFTWARE\
    ODBC\ODBCINST.INI\
    ODBC Drivers\MySQL ODBC 3.51 Driver


 HKEY_LOCAL_MACHINE\SOFTWARE\
    ODBC\ODBCINST.INI\
    ODBC Drivers\MySQL ODBC 5.1 Driver

Using the info found here, you can get at it using the below code (I tested it in Access 97)

Private Sub Command0_Click()    
    If RegKeyExists("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\
                                 ODBC Drivers\MySQL ODBC 3.51 Driver") Then
        MsgBox "3.51"
    ElseIf RegKeyExists("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\
                                 ODBC Drivers\MySQL ODBC 5.1 Driver") Then
        MsgBox "5.1"
    Else
        MsgBox "None"
    End If
End Sub


'returns True if the registry key i_RegKey was found
'and False if not
Function RegKeyExists(i_RegKey As String) As Boolean
    Dim myWS As Object

    On Error GoTo ErrorHandler
    'access Windows scripting
    Set myWS = CreateObject("WScript.Shell")
    'try to read the registry key
    myWS.RegRead i_RegKey
    'key was found
    RegKeyExists = True
    Exit Function

ErrorHandler:
  'key was not found
  RegKeyExists = False
End Function

---

Here are a few possible ideas:

1 You may be able to check the registry and look for specific keys, like this for example: [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\MySQL ODBC 3.51 Driver]

2.You could check their c:\windows\system32 folder for the myodbc.dll, and then check the version information. Here's a link on how to check the version: http://www.vb-helper.com/howto_file_version_info.html

---

If you want to avoid handling versions on a case by case basis you can iterate through the key values, eg..

This function is designed to enumerate through the regkeys for ODBC drivers and just check for the existance of mysql somewhere, if not it will warn the user then take them to the download page, and remind them to get the right version for their architecture (32/64)

Public Function CheckMySQL()

    Dim arrEntryNames()
    Dim arrValueTypes()
    Dim rPath As String
    rPath = "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers"

    Call EnumerateRegEntries(rPath, arrEntryNames, arrValueTypes)

    If Not IsEmpty(arrEntryNames) Then
        For Each strAsk In arrEntryNames
            If (InStr(strAsk, "MySQL")) Then
                strFound = strFound & strAsk & ", "
            End If
        Next
    End If

    If (Len(strFound) = 0) Then
        #If Win64 Then
            MsgBox "You need MySQL Driver *64 bit* - Press OK to get it!"
        #Else
            MsgBox "You need MySQL Driver *32 bit* - Press OK to get it!"
        #End If

        ActiveWorkbook.FollowHyperlink Address:="http://goo.gl/vbm6g", NewWindow:=True

        CheckMySQL = False
    Else
        CheckMySQL = True
    End If

End Function

And you'll need this to enumerate the reg keys (for more on this see http://technet.microsoft.com/en-us/library/ee176771.aspx):

Public Sub EnumerateRegEntries(strKeyPath, arrEntryNames, arrValueTypes)
    Const HKEY_CLASSES_ROOT = &H80000000&
    Const HKEY_CURRENT_USER = &H80000001&
    Const HKEY_LOCAL_MACHINE = &H80000002&
    Const HKEY_USERS = &H80000003&
    Const HKEY_CURRENT_CONFIG = &H80000005&

    Dim objReg As Object
    Dim strComputer As String

    strComputer = "."
    Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
    strComputer & "\root\default:StdRegProv")

    objReg.EnumValues HKEY_LOCAL_MACHINE, strKeyPath, arrEntryNames, arrValueTypes


End Sub