I have a ms access database that reads data from ms sql server and writes it to mysql via the odbc connector driver. It used to work fine with the very occasional timeout then some aspects of the sql server database changed and then I started having timeout issues all the time. I get the dreaded "connection has gone away" error.
sql server changes:
--The sql server database was moved to a 64 bit computer with 64bit sql server.
--Some of the tables were replaced with views that I must use because of xml columns.
Because of this, the sql server seems slower than it used to be.
I have taken various steps to avoid the problem:
--updating the odbc connector driver to the latest version.
--created a form bound to a mysql table that refreshes on a timer to try to keep the connection from going stale.
--used local tables to hold data from sql server first so mysql doesn't have to wait for data from sql server
--where it seems like it would help, used loops in vba to write to mysql one record at a time to avoid joining ms access and mysql tables together in sql which is really slow.
-- step through code to fix any errors it might be encountering which also cause the connection to "go away"
I have looked through this page and didn't find anything useful:
but the timeouts continue at seemingly random but very frequent times. It is very frustrating.