How can I use VBA for ODBC connection to MySQL data source?

[lvman]

How can I use VBA for ODBC connection to MySQL data source?
My company has two MySQL servers. One is for development, and the other is for production. I created an Access application, say MyAcc. MyAcc is connected to MySQL servers through ODBC connection. The connection was made through SSL (Secure Sockets Layer). To activate MyAcc I click “External Data” in the Rainbow, choose “ODBC Database”. Then in the popup window “Get External Data – ODBC Database”, choose “Link to the data source…” another popup window “Select Data Source” appears. There are two tabs, I choose “Machine Data Source”, then choose data source, say, abc-mysql-dev (under the column “Data Source Name” ). A list shows up, and I could choose whichever tables I need from the list.


There is also a data source for production, say, abc-mysql-prod
My question is: How can I use VBA code to link tables in one of 2 data sources: abc-mysql-development and abc-mysql-prod?
The reason for having VBA coding instead of manually set linking tables is that I could add an option control, or a combo box to let users choose product environment or developing environment. The later can be used for testing code or training.
I tried the code line
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN='abc-mysql-dev';UID=;PWD=;LANGUAGE=;DATABASE=", acTable, "tOrder", "tOrder"
But it failed.
Can someone show me the right way to connect MySQL data source in VBA coding?
Your help will be highly appreciated.

[hertfordkc]

instead of transferdatabase.
Check out this link:
http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

[lvman]

The link that you provided looks very useful. I will try it soon, and let you know if the issue is resolved. Thank you very much!

[lvman]

The issue is resolved. Thanks for the help!