Connecting Ms Access Db to Mysql through Vba

Question

I have been trying to connect mysql database to ms Access but no result.I don't think personally I am using the DAo.Connection and the workspace properly. I keep on getting the 3001 connection error when I set mySqlCon. I guess my arguments are not properly set but I was following an example from here.

Function connectingMySql()
   Dim mySqlCon As Dao.Connection
   Dim wrkODBC As Workspace

   Set wrkODBC = CreateWorkspace("newODBCWorkspace", "admin", "", dbUseODBC)

   Set mySqlCon = wrkODBC.OpenConnection("connection1", , , "DRIVER={MYSQL ODBC 5.1 DRIVER};" _
   & "SERVER=testserver.com;port=3306;" _
   & "DATABASE=test;" _
   & "USER=root;" _
   & "PASSWORD=pass;" _
   & "Option=3;")
End Function

More infos:

Running Ms Access 2003

ChipsLetten · Accepted Answer · 2015-06-04 18:34:54Z

1

This MSDN page says that using dbUseODBC will cause a runtime error.

ODBCDirect workspaces are not supported in Microsoft Access 2010. Setting the type argument to dbUseODBC will result in a run-time error. Use ADO if you want to access external data sources without using the Microsoft Access database engine.

Try Set wrkODBC = CreateWorkspace("newODBCWorkspace", "admin", "")

answered Jun 4, 2015 at 18:34

ChipsLetten

2,9531 gold badge15 silver badges28 bronze badges

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

Comments

Coding Enthusiast · Accepted Answer · 2015-06-04 19:30:28Z

0

After much struggle, I fixed it. Basically my arguments where wrong. Now I am getting a connection Error, which means the argument error has been fixed.

Function connectingMySql()
   Dim mySqlCon As Dao.Connection
   Dim wrkODBC As Workspace

   Set wrkODBC = CreateWorkspace("newODBCWorkspace", "admin", "", dbUseODBC)

   Set mySqlCon = wrkODBC.OpenConnection("DRIVER={MYSQL ODBC 5.1 DRIVER};" _
   & "SERVER=testserver.com;port=3306;" _
   & "DATABASE=test;" _
   & "USER=root;" _
   & "PASSWORD=pass;" _
   & "Option=3;")
End Function

answered Jun 4, 2015 at 19:30

Coding Enthusiast

3,9331 gold badge31 silver badges50 bronze badges

Collectives™ on Stack Overflow

Connecting Ms Access Db to Mysql through Vba

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related