I download data from an IBM AS/400 into a table weekly.
This table contains over 2 million records. I need to
find the best way and the fastes way of deleting such a
large numober of records. I have tried both manually
selecting the records and a delete query.
If you routinely want to empty the table altogether and replace it
with a completely new download, I'd really suggest something drastic:
deleting the DATABASE!
That is, use a separate backend database containing *only* this table.
When you're ready to download use the KILL Vba command to delete the
database entirely, then the CreateDatabase method to recreate it.
Download into it using TransferDatabase (creating a new table in the
new database), use it for a week, and then kill again (help... stop me
before I kill again...!)
Deleting two million records will take a long time no matter how you
slice it. The fastest way would be
DELETE * FROM tablename;
since Access lacks the handy Truncate operator that Oracle (and SQL??)
provides. In any case you would also need to Compact the database
after deleting and before inserting, or you'll blow up with the 2
GByte limit in short order!
