[H-GEN] MYSQL commands via MSAccess?

[Paul Gearon]

[ Humbug *General* list - semi-serious discussions about Humbug and     ]
[ Unix-related topics. Posts from non-subscribed addresses will vanish. ]

On Wed, 15 Jan 2003, Tony Melia (DMS) wrote:

> i.e I want to empty table TEST.  TO do this in
> mysql,it is as easy as 'delete from TEST' and takes less than a second. But
> MS access seems to want to delete it record by record and takes about 20
> mins to empty the DB.  Is there a way to pass mysql commands to the server
> 'through' MS access??

I haven't used MSAccess with a server for a few years, so bear with me...

I recall that there's a mechanism for doing a "direct" call on an ODBC
object, though I can't remember the syntax (I last did this in '99).
Something like
  connection.Execute "DELETE FROM TEST",,,odbcDirect
The real command and parameters are definately different.  :-)  The help
pages are pretty good for finding this stuff.

In fact, it doesn't hurt to get familiar with doing direct calls on ODBC
connections.  MSAccess has a bad habit of trying to work out what you
meant and attempting it on it's own.  This often involves downloading
entire tables and performing joins locally, etc (I've seen it pull down
tables of over 100,000 rows just to inner join to a table of a dozen rows.
A "normal" call took over 20 minutes, while a direct call took 1 or 2
seconds).  If you have an SQL database on a server, then you almost never
want to perform queries locally.  Instead, just use MSAccess for your GUI,
and do everything else with SQL sent over ODBC-direct calls.

Regards,
Paul Gearon

Software Engineer                Telephone:   +61 7 3876 2188
Plugged In Software              Fax:         +61 7 3876 4899
http://www.PIsoftware.com        PGP Key available via finger

Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum
immane mittam.
(Translation from latin: "I have a catapult. Give me all the money,
or I will fling an enormous rock at your head.")



--
* This is list (humbug) general handled by majordomo at lists.humbug.org.au .
* Postings to this list are only accepted from subscribed addresses of
* lists 'general' or 'general-post'.  See http://www.humbug.org.au/