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

Answer 1

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

Answer 2

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", "")