Thanks for your reply!
I tried your pass-through idea previously and it did not work. Today I even typed my query directly into the mysql shell (and bypassing MS-Access altogether) and my MySQL server still hung! So we can rule out problems with Access of MyODBC...
I suppose my query is just too resource-intensive for the server (lots of joins), so I must ask again: is there a way to prevent one query from consuming too many resources and hanging the server?
Once again all replies are much appreciated!
Barry
P.S. I use SSH to create a secure "tunnel" between MS-Access & MySQL
P.P.S. I'll check out ADO/VB... thanks!
From: Daniel Kasak <[EMAIL PROTECTED]> To: B F <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED] Subject: Re: ODBC query freezes server Date: Tue, 17 Dec 2002 14:39:22 +1100B F wrote:Hi all,Access will pull the entire tables across the internet and then query the data locally. This is what's taking all the time. Probably Access is requesting a lock until it gets the entire recordset(s).
I am having a problem with MySQL and I hope some nice guru can help me.
I am running MySQL server v3.23.49a on Red Hat Linux v7.1. I also need to run queries on Microsoft Access running on my Win2K machine, using a MyODBC connection over the Internet. However when I run certain queries my whole MySQL server hangs until the query is complete (up to 10 minutes). Thus my entire website hangs while I run reports. :(
My queries use around 5 tables with <100,000 rows in total. Perhaps my query is locking too many tables or using too many threads? I am certain that I am NOT using the maximum # of MySQL network connections (it is set to 1,000 and netstat reports <100 open connections). Is there a way to limit resources so that a single query doesn't bring my server to its knees?
Any and all replies are much appreciated.
Thanks in advance!
Barry
Set up pass-through queries from Access (or better still use ADO / VB).
To set up a pass-through query, design the query, switch to SQL mode, copy the text (Access sometimes deletes it at this next stage) and click Query ==> SQL Specific ==> Pass Through. Then edit the query properties, and set up your connection options to MySQL. You may have to edit the query to get rid of Access' "additions" (eg [] around everything).
This will make MySQL perform the query and send the results to Access, instead of having Access use MySQL as a dumb data storage device.
The second option (ADO / VB) is a bit more complicated but more versatile. I won't go into the details here, but you can search on the 'net for ADO - you'll find plently of examples.
Note that using Access to query MySQL over the internet isn't exactly secure. Someone correct me if I'm wrong here, but doesn't this send clear-text passwords across the internet?
--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
