VBA/MySQL issue using ODBC connector

Question

I have been struggling with this for a few days now. Any help much appreciated.

Trying to connect to MySQL database using Excel VBA, on a PC with the following:

Excel 2007 Windows 7 x64 Home Premium MySQL 5.5 MySQL ODBC Connector 5.1, 64 bit

In the Excel VBA I have referenced Microsoft ActiveX Objects 2.8 Library.

The VBA I am using to connect is:

Dim oConn As ADODB.Connection
Public Sub ConnectDB()
    Set oConn = New ADODB.Connection
    oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=localhost;" & "DATABASE=test;" & "USER=root;" & "PASSWORD=PWhere;" & "Option=3"
End Sub

Every time I run this I get the error dialog: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

MySQL service is definitely running.

I have used Windows Data Source Administrator to check that MySQL ODBC Connector 5.1 is present and correct: it is, checks out OK when I try to create a DSN in this way.

Looking through the VBA project reference options, I note the options to reference a whole host of different ADO Libraries, including (Multi-dimensional) options and library versions 2.0,2.1,2.5,2.6,2.7, and 6.0 - maybe the answer lies in one of these?

Any more details required, let me know.

Answer 1

You need to use either the 32 or 64 bits version depending on the version of Excel, not Windows. So even if you run Windows 7 64 bits, I believe Excel 2007 only comes in 32 bits so you would need to use the 32 bits mysql connector.

See also this bug report that is similar to your issue.

Answer 2

I got similar message when moved my application to other system with different version of driver - it looks like misspelled driver name causes identical message. To find correct driver name and make application driver version independent I use the following code:

    Public Function Get_Driver() As String

    Const HKEY_LOCAL_MACHINE = &H80000002
    Dim l_Registry As Object
    Dim l_RegStr As Variant
    Dim l_RegArr As Variant
    Dim l_RegValue As Variant

    Get_Driver = ""
    Set l_Registry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    l_Registry.enumvalues HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", l_RegStr, l_RegArr

    For Each l_RegValue In l_RegStr
        If InStr(1, l_RegValue, "MySQL ODBC", vbTextCompare) > 0 Then
            Get_Driver = l_RegValue
            Exit For
        End If
    Next
    Set l_Registry = Nothing
End Function