Re: Unstable links to MySQL database from Access

[Brett Bavar]

I think I've got it! I had to use a completely different function that I just discovered. To link to one of my tables, the code looks like this:

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=" & myDSN, acTable, tableName, tableName, False, True

Parameters:
acLink - this specifies that you want to link tables, rather than import or export
"ODBC Database" - this is the type of database you are linking from
"ODBC;DSN=" & myDSN - this is the connection string to the database you are linking from
acTable - this specifies that you are linking to a table, rather than a query or other Access object
tableName - this is the name of the table in the source database
tableName - this is the name you want to call the linked table in the destination database
False - this specifies that you do not want structure only, but rather structure and data
True - this specifies that you want to save the login info in the connection string

That final parameter is the key to making this work. This saves the login information so that the Access frontend can properly access the MySQL database later on, instead of losing this important info every time you close the database, like Access seems to do by default.