Fastest way to insert records into SQL from Acess

[BradEdwards]

I have 24 facilities in which we download a journal.mdb every night. In the morning I run a program that I wrote in VB that consolidates all 24 journal.mdb files into one file. This creates one big journal.mdb file which has anywhere from 15-20,000 records in it. I added to my VB program a section which INSERTS those records into a database on our SQL Server for future calculations. However, it takes about 4-5 minutes to upload those 15-20,000 records into the SQL Database. Is this normal or is there a faster way to do this. I've used SQL Pass Through queries before which queries and deletes data quicker but when I tried to write a pass through query to insert data it said it couldn't find the source table which is local to Access. It's not that big of a deal. I just wanted to know if there was a faster way. In VB I set an strSQL variable to "Insert ......" then run db.execute strSQL.

One other question? If there is not a faster way, is there anyway I can add a progress bar or some sort of status box that displays the number of records transferred so far just so the user doesn't think the program is locked up. Any help would be much appreciated. Thanks.

 

[tlbroadbent]

Have you tried inserting the records into a linked SQL table using an Append query? That should almost always be faster than inserting each record individually with db.execute statemnts in VBA code. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[BradEdwards]

Hey Terry,

I am using a linked table but I'm still using the db.execute in my vb code. Here is what my code looks like:

strSQL = "INSERT INTO tblJournal SELECT * FROM Journal ORDER BY Journal.BRN, Journal.BOX, Journal.SEQ"
db.execute strSQL

tblJournal is the linked table to the SQL Database.

Is it faster to just setup an Append Query and run that as opposed to using db.execute??

You know I just thought of something. I don't think I really need to order the data to put it into tblJournal. I may be able to save a little time by taking the ORDER BY out. DUH Thanks Terry.

 

[tlbroadbent]

I believe the Append query may be faster because you'll be using Native JET functionality. However, I have no data to prove that is so.

Another performance consideration is dropping the indexes, if any exist on the SQL table, prior to inserting the records. Then recreate the indexes. This however is a tradeoff due to the time required to build indexes. You may not gain anything. If the SQL table is very large, and you are appending a few thousand rows, you'll probably use more time rebuilding than gained on the insert. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.