Solved

ms access timeout problems with mysql odbc connector driver

Posted on 2014-09-22
4
677 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 49

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

740 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