Connecting WAMP MySQL with VBA

Question

I am trying to connect WAMP MySQL with VBA. I have the tables created in the database using PHPMyAdmin. I have referenced Microsoft ActiveX Data Objects 2.8 Library and have installed MySQL Connector/ODBC 5.1 driver. I am not able to connect to mysql. My code is

Dim oConn As ADODB.Connection
Private Sub ConnectDB()
    Set oConn = New ADODB.Connection
    oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
        "SERVER=127.0.0.1;" & _ ' Tried localhost too
        "DATABASE= dbtest;" & _
        "USER=root23;" & _
        "PASSWORD=pass;" & _
        "PORT=3306;"& _
        "Option=3"
End Sub

The error is

Microsoft ODBC Driver Manager. Data Source name not found or no default driver specified. All services in my WAMP are running. My http://localhost/phpmyadmin/ page opens fine. I also tried putting PORT=3306 too.

"I am not able to connect to MySql" - are you getting an error message or similar? What's happening. Your connection string looks okay. — dash
Actually, I think you might be missing the port. By default, I think it's 3306; try: "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=dbtest;UID=root;PASSWORD=pass;Port=3306;Option=3" - that's how I have all my MySql db connections defined in VBA. — dash
The only difference I can see is the space in the "Database= dbTest". Where did you get the ODBC drivers from? It's definitely 5.1 you are running and not 3.51? They are the only things I can think of I'm afraid as your MySql instance is obviously fine. — dash

dash dash · Accepted Answer · 2011-12-09T21:59:04

Here's how we define our VBA connection strings for MySql:

"DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=dbtest;UID=root;PASSWORD=pass;Port=3306;Option‌=3"

From our chat, it looks like 3.51 is the version that works for you, too. It can be downloaded at the bottom of the page here

If I get a minute I'll try a local upgrade to 5.1 and see if it's a driver specific issue. I'll comment here if I find anything.

Connecting WAMP MySQL with VBA

1 Answers