Delete all records - fast

[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!

 

[Allen Browne]

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.

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?

 

[Newbie]

docmd.runSQL "Delete * FROM Data1

HTH

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?