VBA / MySQL issue using ODBC connector

Question

I struggled with this for several days. Any help is greatly appreciated.

Attempting to connect to a 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 Excel VBA, I reference the Microsoft ActiveX Objects 2.8 library.

The VBA that I use to connect:

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 a dialog box with the error: "[Microsoft] [ODBC driver manager] Data source name not found, and default driver not specified"

MySQL service is definitely working.

I used the Windows Data Source Administrator to verify that the MySQL ODBC Connector 5.1 is present and fixed: it checks OK when I try to create a DSN this way.

Looking through the link options for the VBA project, I mark the link options for the entire set of different ADO libraries, including the (multidimensional) parameters and versions of the libraries 2.0.2.1, 2.5.5.2.6.2.7 and 6.0, maybe the answer lies in one of them?

More details are required, let me know.

Answer 1

You need to use version 32 or 64 bits depending on the version of Excel , not Windows. Therefore, even if you ran the 64-bit versions of Windows 7, I believe that Excel 2007 only comes in 32 bits, so you will need to use the 32-bit mysql connector.

See also the bug report report , which is similar to your problem.

Answer 2

I got a similar message when transferring my application to another system with a different version of the driver - it looks like the driver name with an error causes an identical message. To find the correct driver name and make the 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