Posted by Thomas Bartkus on 01/03/06 19:12

"Mike Easter" <username@domain.com> wrote in message
news:Xns9738CBC307C5Busernamedomaincom@216.196.97.136...
> Mike Easter <username@domain.com> wrote in
> news:Xns9738B2FCEE7D4usernamedomaincom@216.196.97.136:
>
> > I've built a web application that uses MySQL to store data, PHP as a
> > web front-end, and Microsoft Access 2000 as an administrative
> > front-end. I prefer to have Microsoft Access as my administrative
> > front-end for convenience and security (the web portion of the
> > application is relatively small).
> >
> > At times I need to empty my MySQL tables from Microsoft Access, and
> > the performance is awful. I would prefer to use TRUNCATE for speed,
> > but this isn't available in Microsoft Access (as best as I can tell).
> > Neither is OPTIMIZE, and I would like to compress my tables from
> > Microsoft Access if possible.
> >
> > Has anyone else been in my situation? Are there any clever workarounds
> > that will permit me to trigger TRUNCATE/OPTIMIZE queries from Access?
> >
> > Any help/opinions appreciated.
> >
>
> For those curious about the answer, I actually found help in Microsoft
> Access Help under "stored_procedure." Specifically, there is very good
> documentation on "Send commands to an SQL database using a pass-through
> query," which permitted me to send a TRUNCATE command through an Access
> query. Because TRUNCATE is a combination of a table drop and create, I
> don't even need the OPTIMIZE command. Talk about an improvement in
> performance! Those DELETEs were killing me!

Glad you discovered the answer for yourself!
PassThrough queries using native MySQL syntax is hard to beat.

I frequently create a single PassThrough query in Access and then use VBA to
"stuff it" with different MySQL commands. You want to bypass MS Jet and pass
as much work as possible to the server via native mysql flavor of SQL. And
if you are an MS Windows house, Access (PassThrough queries) are a great way
to store procedures for version of MySQL prior to 5.0 which lack that
facility.

-Thomas Bartkus

[Back to original message]