- MS Access/MyODBC troubles with linked tables
Tested on MyODBC 3.51.14 but it should work for 3.51.12 too.
Related bug report: Please see the external resources link in the margin.
In MyODBC 3.51.14 enable AutoReconnect option!
How to repeat:
- Create a mysql table
- Open a new Access database and link the mysql table in it
- Open the table from within Access in order to verify that it is accessible, and then close it
- Use the mysqladmin tool to kill the connection (thus simulating a crash and/or restart/reboot) - or just restart the service
- Try to open the table from within Access again. A "MySQL server has gone away" message is shown and all further efforts to connect fail
- Restart Access and reopen the database. The table is now accessible and a new mysql connection will be made.
Access, with default settings, exhaust its retry limit and considers the connection to be dead.
Access does not follow its own rules not ignoring missing MSysConf table: "Reading the remote connectivity configuration table: When starting a connection, Access executes the query SELECT nValue FROM MSysConf WHERE Config = 101 against the data source. Any errors during execution/fetching of this query are ignored. This table is not required to exist in the data source, but allows for Access-specific configuration options."
To fix this situation: Change settings as in a picture (put it somewhere) increasing ODBC refresh interval (prevents Access from exhausting Number of retries quickly) and disabling automatic updates (for the same reason). This works fine on my machine.
Finer tuning of Jet engine can be done by modifying registry entries in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC
General info on MS Access: Please see the external resources link in the margin.