- MS Access/MyODBC troubles with linked tables
Tested on MyODBC 3.51.14 but should work for 3.51.12 too.
Related bug report please see the external resources link Bug Report in the margin.
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 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."
How to fix this situation: I changed settings as in the picture (put it somewhere) increasing ODBC refresh interval (prevents Access from exhausting Number of retries quickly) and disabling automatic updates (for the same reason).
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.￼