VBA connect to MySQL DB

Question

I try to connect my Excel Spreadsheet to my MySQL DB hosted locally for the moment. I'm using WAMPSERVER.

Here is my VBA code :

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

I created my db "test" on through phpMyadmin... I have an error when I run the code. Do you have an idea?

@mehow It seems to be an Execution error '-2147467259 (80004005)', automation error and unspecified error — Henri
– Henri, Commented Mar 13, 2013 at 16:39
have you tried using UID and PWD instead of USER and PASSWORD ? + check your mysql version maybe its older than 5.1 so you may need to change your driver version to ie. 3.51 or find a matching String here — user2140173
– user2140173, Commented Mar 13, 2013 at 16:46
@mehow I changed USER and PASSWORD as UID and PWD and I got the same error... I also installed an older driver version 3.51 and it is not working neither... — Henri
– Henri, Commented Mar 13, 2013 at 16:52
does debugger point at the string when its throwing the error? — user2140173
– user2140173, Commented Mar 13, 2013 at 17:00

Henri · Accepted Answer · 2013-03-15 16:57:46Z

2

The problem was due to a wrong references defined. In the VBE, I had to click on Tools>References and check the "Microsoft ActiveX Data Objects 6.1 Library", and only this one.

The macro is properly running now under WAMP (with default parameters Username = root and Pwd = "") on Windows 7 with the following code :

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

answered Mar 15, 2013 at 16:57

Henri

1,8016 gold badges27 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA connect to MySQL DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related