How to create Access to MySQL using VBA (1 Viewer)

[AccessProgram]

Good day to all. I want to ask how can I connect Access to Mysql Server using VBA.

Thanks!

 

[Banana]

Hi, if you google & search the forums on "DNS-less connection" and look at Carl Prothman's site or connectionstrings.com you'll be able to use the info to build DNS-less connection with the given connection strings.

 

[AccessProgram]

That was very helpful. I have a follow up question, how can I now link the tables from mysql to Access after having created a connection?

 

[AccessProgram]

To connect to mysql server, first I will create a DNS thru vba, second use below connection string

*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"

I have question on this connection string format: Is that the proper driver to use? Is the port 3306 fixed? What is Option=16384 and Stmt=;.

And suppose I have two computer: server1(mysql server BE) and computer1 (FE), how will I use the connection string to specific that my mysql database is in server1?

 

[AccessProgram]

assistance for above please!