Solved

ms access timeout problems with mysql odbc connector driver

Posted on 2014-09-22
4
631 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 45

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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Ever notice how you can't use a new drive in Windows without having Windows assigning a Disk Signature?  Ever have a signature collision problem (especially with Virtual Machines?)  This article is intended to help you understand what's going on and…
When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

17 Experts available now in Live!

Get 1:1 Help Now