Link to home
Start Free TrialLog in
Avatar of StellerSystems
StellerSystems

asked on

ms access timeout problems with mysql odbc connector driver

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:

http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

but the timeouts continue at seemingly random but very frequent times. It is very frustrating.

any ideas?
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of StellerSystems
StellerSystems

ASKER

i have managed to reduce the odbc timeouts to the point where to sync program works ok. i found a flaw in a query thaat was causing most of the last timeouts.

I am going to investigate Integration Services since I feel my program is a bit fragile. when i first wrote this, sql server 2000 wasn't good at that.
i am spitting the points based on both answers contained helpful ideas but neither directly solved my problem.