Access Equivalent to SQL's Truncate Table

[Paul]

Hi Guys, can anyone help?

Is there an access equivalent to SQL's truncate table
command? I need to very quickly delete all records from a
table but keep the structure of the table. Running a
delete query takes a very long time as there are
thousands of records. I know I could just copy the
structure of the table to a different name, delete the
table and rename the copy but I'm trying to automate the
process and want to avoid it if I can due to table
relationships.

Any help will be greatly appreciated.

Many Thanks
Paul

 

[Tim Ferguson]

I need to very quickly delete all records from a
table but keep the structure of the table. Running a
delete query takes a very long time as there are
thousands of records.

A plain and simple

Currentdb().Execute "DELETE FROM TempTable;", dbFailOnError

takes too little time to count on 320k records -- how big is your table and
how much of a hurry are you in? For comparison, it takes about 100 seconds
to fill it up again with a simple DAO.Recordset.AddNew loop -- this is a
pretty aged 700 MHz system, so a more recent processor would cut times
greatly.

B Wishes


Tim F

 

[TC]

A plain and simple

Currentdb().Execute "DELETE FROM TempTable;", dbFailOnError

takes too little time to count on 320k records -- how big is your table and
how much of a hurry are you in? For comparison, it takes about 100 seconds
to fill it up again with a simple DAO.Recordset.AddNew loop -- this is a
pretty aged 700 MHz system, so a more recent processor would cut times
greatly.

"DELETE FROM table" used to be slow in earlier versions, because it
literally deleted every record individually. It was optimized in Access 97
(I think) to do it more efficiently. Perhaps he's using an old version.

TC

 

[Guest]

Thanks for your help guys, i'll give that a try. It is
part of a whole string of deletes and imports that I need
to do and it was taking longer than I'd anticipated but
hopefully this speed things up. I'm deleting about 10,000
records at a time.

Thanks again
Paul