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?
LVL 1
StellerSystemsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Have you thought in using the Integration Services from SQL Server?
It can do migration of data in so a simple way and gives you a better control on all these tasks (import/export).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
"The sql server database was moved to a 64 bit computer with 64bit sql server."
"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."

It costs nothing, is simple to do, and has a good probability of solving the problem.
Which is the very definition of the first troubleshooting step to take.

Are you CERTAIN you are not having a NIC/cable/switch related network problem?
Check the event viewer on both machines for network link up/down events (if your hardware logs them--my Dell servers do)  Switch the cable on the server and switch the port that it is jacked into on the switch.  Ideally, use the same port the old server was in!

Does that change anything?  A flakey NIC/cable/switch won't disturb file access if the drops are sub-second long, but it'll play hell with MS Access.  This may also seem dumb but:  You are certain that your cabling run is less than 93 m in length, right?   Have you checked the MySQL logs to see if it is noticing network interruptions?

And, the old MySQL server--what was its query timeout value?
What about the new one?  Are they the same?
0
StellerSystemsAuthor Commented:
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.
0
StellerSystemsAuthor Commented:
i am spitting the points based on both answers contained helpful ideas but neither directly solved my problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.