excel vba mysql ado connection

4
votes

I'm trying to establish an ADO connection between excel on my local machine and a MySQL database on my server.

In the examples I've seen (here and here, for instance) there's a driver of the form MySQL ODBC 5.x Driver. It seems that after installing the latest mysql connector / odbc download (32-bit, to match my msexcel) the relevant registry driver files HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Driver\ are now named 'SQL Server' and 'SQL Server Native Client 11.0.' I'm not having success establishing a connection to MySQL with either of these.

My VBA

Sub connect()
Dim Password As String
Dim SQLStr As String
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String

Set rs = CreateObject("ADODB.Recordset") 'EBGen-Daily
Server_Name = "184.154.225.243"
Database_Name = "*******" ' Name of database
User_ID = "********" 'id user or username
Password = "*******" 'Password
Port = "3306"

SQLStr = "SELECT * FROM *******"

Set Cn = CreateObject("ADODB.Connection")
Cn.Open "Driver={SQL Server};Server=" & _
        Server_Name & ";Port=" & Port & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic

Upon running the above, I receive error [Microsoft][ODBC SQL Server Drive][DBNETLIB]SQL Server does not exist or access denied. The error for the 'native client 11.0' driver is Could not open a connection to SQL Server[53].

I've tested the connection parameters in MySQL workbench and all is functional. What's going on?

1
mysqlexcelvbaado
Driver={SQL Server} indicates that you are trying to use MSSQL drivers instead of MySQL. Go to Run -> odbcad32 -> Drivers tab and see if the MySQL driver is listed as an installed driver – slayernoah
Also, based on whether you are using the 32 bit or 64 bit version of Microsoft office, you would want to download the same version from the download page that you have linked to – slayernoah

1 Answers

10
votes

  1. Check if you are using the 32 bit or the 64 bit version of Microsoft Office.

  2. Based on the above, download and install the appropriate MySQL driver from the download link

  3. Once the ODBC driver installation is complete, check the ODBC snap in to see the driver is listed as installed.

If you are using a 32 bit OS, then everything is 32 bit. Use Run -> odbcad32.exe -> Drivers tab.

If you are using a 64 bit OS, and Microsoft Office is 32 bit, then use c:\windows\syswow64\odbcad32.exe -> Drivers tab.

If you are using a 64 bit OS, and Microsoft Office is 64 bit, then use Run -> odbcad32.exe -> Drivers tab.

ODBC Drivers

If the MySQL drivers are properly installed, they should appear as shown above

  1. Create a System DSN using the ODBC snap in with the MySQL driver listed above and test the connection to see if it works.

  2. Use the same parameters when you try to create an ODBC from within VBA.

Example:

Driver={MySQL ODBC 5.3 ANSI Driver};Server=localhost;Database=myDataBase;
User=myUsername;Password=myPassword;Option=3;
  1. Once it is established that you can successfully create a connection to the MySQL server, then change the driver name in the registry (make sure to update both the registry keys) and try using the new name you give such as SQL Server.

Remember: On a x64 bit system for a x32 bit drivers: 

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\ODBC Drivers

A restart may be required after changing the driver name.