Connect Access database to Remote MySQL database
To connect to a local database (using MyODBC Driver)
oConn.Open “Driver={mySQL};” & _
“Server=MyServerName;” & _
“Option=16834;” & _
“Database=mydb”
To connect to a remote database
oConn.Open “Driver={mySQL};” & _
“Server=db1.database.com;” & _
“Port=3306;” & _
“Option=131072;” & _
“Stmt=;” & _
“Database=mydb;” & _
“Uid=myUsername;” & _
“Pwd=myPassword”
To connect to a local database (using MySQL ODBC 3.51 Driver)
oConn.Open “DRIVER={MySQL ODBC 3.51 Driver};” & _
“Server=myServerName;” & _
“Port=3306;” & _
“Option=16384;” & _
“Stmt=;” & _
“Database=mydatabaseName;” & _
“Uid=myUsername;” & _
“Pwd=myPassword”
Or
oConn.Open “DRIVER={MySQL ODBC 3.51 Driver};” & _
“SERVER=myServerName;” & _
“DATABASE=myDatabaseName;” & _
“USER=myUsername;” & _
“PASSWORD=myPassword;”
Note: When you first install MySQL, it creates a “root” user account (in the sys datbase’s user table) with a blank password.
it is better to create a DSN and use it to avoid ambiguity. these things arise due to improper match of ODBC and oledb drivers. they keep changind while installing ADO 2.8 has an additional patch to be downloaded from microsoft.
since i don’t use MYSQL, but use sqlserver, oracle,access and Visual Foxpro. in all these cases, once you create a DSN, the trouble is overcome.
then you can investigate the non-DSN connection string. use ADODC to build the connection string and check out.
Ravindra M.G.