VBA : Connection to mysql, issue on ADODB.Connection

Question

I am trying to connect to Mysql via VBA in access. I have created my DSN on my computer, the test connection works well. In VBA I am doing this :

Dim S As String
    Set oConnect = New ADODB.Connection
    S = "DRIVER={MySQL ODBC 5.3 ANSI Driver};" & _
        "SERVER=localhost ;" & _
        "DATABASE=myDataBase ;" & _
        "USER=root;" & _
        "PASSWORD=root;" & _
        "Option=3"
    oConnect.Open S

I am getting this error :

Defined type by user is not defined.

I have referenced Microsoft Activex Data object 2.8.

Thanks.

Do you have some code around this? Because you only define a string here and the error message can't be on this one as it is a predefined type. +1 for @Hearner comment — R3uK
– R3uK, Commented Aug 10, 2015 at 7:45

Hearner · Accepted Answer · 2015-08-10 07:43:18Z

1

The right way is :

Driver={mysql};  & _
database=nameDB; & _
server=NameServer; & _
uid=NameUser; & _
pwd=PassWord; & _
option=16386;

Change USER for uid and PASSWORD for pwd

And don't forget to active the library : Microsoft AtiveX Data Objects 2.x Library.

Change your S to :

 S = "DRIVER={MySQL ODBC 5.3 ANSI Driver};" & _
     "SERVER=localhost ;" & _
     "DATABASE=myDataBase ;" & _
     "uid=root;" & _
         "pwd=root;" & _
         "Option=3;"
oConnect.ConnectionString  S
oConnect.Open

answered Aug 10, 2015 at 7:43

Hearner

2,7053 gold badges19 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

VBA : Connection to mysql, issue on ADODB.Connection

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