Solved

ms access timeout problems with mysql odbc connector driver

Posted on 2014-09-22
4
671 Views
Last Modified: 2014-09-29
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?
0
Comment
Question by:StellerSystems
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40338575
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
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 total points
ID: 40339515
"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
 
LVL 1

Author Comment

by:StellerSystems
ID: 40351421
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
 
LVL 1

Author Closing Comment

by:StellerSystems
ID: 40351435
i am spitting the points based on both answers contained helpful ideas but neither directly solved my problem.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question