|
|
Posted by Thomas Bartkus on 09/27/72 11:36
"Mike Easter" <username@domain.com> wrote in message
news:Xns9740E5665887Cusernamedomaincom@216.196.97.136...
> "Thomas Bartkus" <thomasbartkus@comcast.net> wrote in
> news:ScSdndY1j466LSfenZ2dnUVZ_sGdnZ2d@telcove.net:
>
> > 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
>
> Thanks for your response! My next challenge is to speed up a costly
> insert query. I'm inserting approximately 700 records to a MySQL table,
> and am doing so using an MS Access append query. Not so efficient! At
> some point I'd like to use a pass-through to MySQL, but am stumped as to
> how to refer to a Jet object in my SQL. I've tried exporting to text and
> then using the text file, but unfortunately the records include free-
> form text, so my delimiters aren't reliably unique.
I don't know why (or how!) you want to attach a Jet object from a SQL
statement.
The best way to handle bulk inserts is to execute a LOAD DATA INFILE against
a text file. You can accomplish this with a PassThrough query just so long
as you can push the data file to the machine hosting MySQL.
We are an MS Office house with MySQL on a Linux server. I [pscp] copy .csv
(text) files from the Windows workstation to the Linux server then execute
that "LOAD DATA INFILE" statement from an MS Access PassThrough query. The
whole deal can be accomplished from inside Access with a bit of VBA. This is
definitely the way to fly for large bulk inserts.
Your exported text might be messy, but LOAD DATA INFILE is really quite
flexible with respect to delimiters. Of course, if the text is *really*
messy, you might need some VBA pre-formatting. But again, if speed is the
goal, bulk inserts are definitely the answer.
Thomas Bartkus
Navigation:
[Reply to this message]
|