Size Limitation Options - Workarounds?

[smedvid]

I have an MS Access 2003 DB with 5 small lookup tables and a external Linked in primary data table (1.6 million rows). I have a VBA Procedure that loops through the 1.6 million rows via a record set; then Inserts 5 or 6 fields into a seperate external linked table. What is confuzzzing is... I am hitting the size limitation on the Processing MS Access DB; which has virtually no data. My guess is that the Insert transactions are stored in a system table and clog up my size. So, any options to suppress the recording of these transactions?

Also, when I get to 1.9GB the DB hangs and is corrupt.

I really do not have time to migrate to SQL Server; since this is due yesterday. Thanks in Advance!

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Craig0201]

I'll ask the simple question.....

Why the recordset? Can you not achieve the insert by a (series of) query(ies)?

I'm guessing yes.

C

 

[smedvid]

Unfortunately, the record set is required since I have multiple function calls for each lookup. Then I perform an insert for each record.

However, my first attempt was to perform everything in a single SQL query. It worked! However, I did find that the query took three times as long to process.

As a test I processed 100,000 records. The single insert code method took 22 minutes. The query approach took over 48 minutes.

My solution was to manually run the single insert process in blocks of 500,000 until I got all 2.5 million rows done. After each 500,000 rows processed; I would manually compact the database.

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...