0

I am using ODBC to link some tables to a MySQL database hosted remotely. It's not a very good host, so it fails quite often. When this happens, it will hang Access (version 2003) for about 20 minutes, and then finally come up with an error (3146), saying that the connection unexpectedly closed. The only way I can get out of it is by End Tasking access. This can be frustrating, especially if I just wrote some code that wasn't saved yet. Is there a way to cancel the odbc operation, perhaps by stopping some sort of service?

clum
  • 469
  • 4
  • 20
  • I'm putting this in comments because it doesn't directly answer your question. I don't recommend using ODBC over WAN or other slow or unreliable connections. It's much better to use ADO where you can control the timeout lengths on the connection object and the command objects and make decisions about what to do when those timeouts occur. It takes a fair amount of code since you cannot link tables via ADO. – HK1 Sep 16 '13 at 17:40
  • @HK1 - I don't have any experience with ADO, so it's a bit of an overkill for what I need to do. My database is basically separated on each user's computer, I only need to keep certain settings common between all of the computers that use it. So I just made an online database on the company's webserver that stores these settings, and the database copies that information into a local table just before it's used. And there is a form where the users can edit that online table, which I would imagine would be quite difficult to do without a linked table. – clum Sep 16 '13 at 21:00
  • You said: `I don't have any experience with ADO so it's a bit of an overkill for what I need to do.` There's no direct relationship between something being overkill and not knowing how to do it. Using ADO and allowing edits isn't nearly as difficult as what you might think, although I'll admit it can seem complex and difficult if you're not familiar with it. But it's not overkill - It's really the only solution that's going to work reliably in your case. ODBC over WAN is simply not a good solution. – HK1 Sep 16 '13 at 21:04
  • @HK1 - okay, thanks. For now I will probably just leave it as is, and then change it once my users start complaining... – clum Sep 17 '13 at 05:10
  • @HK1 - you were right - I had so many problems with the linked table that I ended up using ado, which so far seems more stable. I just make it copy the online tables to local tables at startup, and if someone changes anything, then it uploads the changes to the online table. And ADO was not a big deal to learn, it is very similar to DAO. – clum Sep 18 '13 at 00:29
  • Glad to hear it. ADO is very similar to DAO, but in many ways more flexible and more powerful, especially when working with Database Servers such as SQL Server and MySQL. – HK1 Sep 18 '13 at 00:37

0 Answers0