Deleting and Recreating Access Tables

  • Thread starter Thread starter BarryC
  • Start date Start date
B

BarryC

I have a program that imports data into temp tables, processes it and
places the results
into final tables. My problem is that it is really slow. Each temp
processing table has almost 2 million records in it. I have identified
the deletion of these temporary records as a bottleneck in the speed.


My first thought is to drop the tables and recreate them instead of
deleting the records.


Any thoughts on this? Better ideas? I've seen comments that this isn't
good for DB stability.
 
I have a program that imports data into temp tables, processes it and
places the results
into final tables. My problem is that it is really slow. Each temp
processing table has almost 2 million records in it. I have identified
the deletion of these temporary records as a bottleneck in the speed.


My first thought is to drop the tables and recreate them instead of
deleting the records.


Any thoughts on this? Better ideas? I've seen comments that this isn't
good for DB stability.
Click to expand...

Even better - create a new .mdb DATABASE just for the temp tables, and
Kill the database when you're done with it. This will avoid the slow
step of deleting records.

One helpful way to do this is to have a template .mdb file containing
(empty) temp tables; copy it to a new database, link to it, run your
imports and so on, then kill the copy.

John W. Vinson[MVP]
 
John Vinson said:
Even better - create a new .mdb DATABASE just for the temp tables, and
Kill the database when you're done with it. This will avoid the slow
step of deleting records.

One helpful way to do this is to have a template .mdb file containing
(empty) temp tables; copy it to a new database, link to it, run your
imports and so on, then kill the copy.
Click to expand...

Tony Toews has a sample of doing this at
http://www.granite.ab.ca/access/temptables.htm
 
Back
Top