Hi all.
I have a MySQL database hosted on my ISP and have been using Access for simple queries and reports. Linked tables through ODBC.
Now I need to do more serious stuff and the fact that my ISP has a 10 second timeout on the ODBC connections is really getting me upset. First thing I did was talk to them but they don't wanna hear about it. They seem to think persistent connections are a big issue for a shared server.
So I used DoEvents to simulate forking out a thread; in the DoEvents loop I count to 5 sec. and run a quick count(*) query on a linked table. This way I'm able to keep the connection open for very long periods, even hours at a time.
The problem happens when my user does anything on my forms that hatls execution, and the code can't return to the DoEvents loop in time for a new hit. Simple things like holding the scroll bar with the mouse, or holding open a combo box. They don't have to do it for very long, it's all a matter of timing.
Anyway, I've racked my brains - and the web - and can't find a way to make this solution more stable. A persistent connection over ODBC when your DBA doesn't want it is poor solution, anyway.
Is there a way to create and maintain the linked tables WITHOUT using ODBC? Something my ISP wouldn't frown at?
Thanks for your attention.
Rgds.
Eduardo Baldino
I have a MySQL database hosted on my ISP and have been using Access for simple queries and reports. Linked tables through ODBC.
Now I need to do more serious stuff and the fact that my ISP has a 10 second timeout on the ODBC connections is really getting me upset. First thing I did was talk to them but they don't wanna hear about it. They seem to think persistent connections are a big issue for a shared server.
So I used DoEvents to simulate forking out a thread; in the DoEvents loop I count to 5 sec. and run a quick count(*) query on a linked table. This way I'm able to keep the connection open for very long periods, even hours at a time.
The problem happens when my user does anything on my forms that hatls execution, and the code can't return to the DoEvents loop in time for a new hit. Simple things like holding the scroll bar with the mouse, or holding open a combo box. They don't have to do it for very long, it's all a matter of timing.
Anyway, I've racked my brains - and the web - and can't find a way to make this solution more stable. A persistent connection over ODBC when your DBA doesn't want it is poor solution, anyway.
Is there a way to create and maintain the linked tables WITHOUT using ODBC? Something my ISP wouldn't frown at?
Thanks for your attention.
Rgds.
Eduardo Baldino