VBA/MySQL issue using ODBC connector

mysql vba excel odbc dsn
ploddingOn picture ploddingOn · Mar 22, 2012 · Viewed 9.8k times · Source

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

assylias picture assylias · Mar 22, 2012

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.

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More