Delete all records - fast

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a VBA procedure (below) that deletes all of the records from a table. The only problem is that it is slow for large tables (>500,000 records). Is there any faster way to delete all records?

Set db = Currentdb
Set rst1 = db.OpenRecordset("Data1")
db.Execute "delete * from [Data1]"
Set rst1 = Nothing
Set db = Nothing

OR ... Another idea - if someone could help with the code:
I could create a copy of the table [Data0] (with no records). Whenever I want to clear out [Data1], I could delete [Data1] and copy [Data0] to [Data1]. What is the VBA code to copy the structure of one table to another?

Thanks!
 
Don't open the recordset first. Just execute the action query:
dbEngine(0)(0).Execute "delete from [Data1];"


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

George said:
I have a VBA procedure (below) that deletes all of the records from a
Click to expand...
table. The only problem is that it is slow for large tables (>500,000
records). Is there any faster way to delete all records?
Set db = Currentdb
Set rst1 = db.OpenRecordset("Data1")
db.Execute "delete * from [Data1]"
Set rst1 = Nothing
Set db = Nothing

OR ... Another idea - if someone could help with the code:
I could create a copy of the table [Data0] (with no records). Whenever I
Click to expand...
want to clear out [Data1], I could delete [Data1] and copy [Data0] to
[Data1]. What is the VBA code to copy the structure of one table to
another?
 
docmd.runSQL "Delete * FROM Data1

HTH

George said:
I have a VBA procedure (below) that deletes all of the records from a
Click to expand...
table. The only problem is that it is slow for large tables (>500,000
records). Is there any faster way to delete all records?
Set db = Currentdb
Set rst1 = db.OpenRecordset("Data1")
db.Execute "delete * from [Data1]"
Set rst1 = Nothing
Set db = Nothing

OR ... Another idea - if someone could help with the code:
I could create a copy of the table [Data0] (with no records). Whenever I
Click to expand...
want to clear out [Data1], I could delete [Data1] and copy [Data0] to
[Data1]. What is the VBA code to copy the structure of one table to
another?
 
Back
Top