Solved

ms access timeout problems with mysql odbc connector driver

Posted on 2014-09-22
4
644 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 46

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now