Deleting all table records through VBA / DAO 3.6

[Joel Wiseheart]

I use the following code to delete all records in a table
through VBA code. The problem is that since the table
contains over 47,000 records, it takes 3 minutes to run.
Is there a faster way to delete all these records?

Dim db As DAO.Database
Dim rstNew As DAO.Recordset

Set db = CurrentDb
Set rstNew = db.OpenRecordset("t_SOWOShortTrackComments")

With rstNew
If .RecordCount > 0 Then
.MoveFirst
Do
.Delete
.MoveNext
Loop Until .EOF
End If
End With

 

[Tim Ferguson]

Is there a faster way to delete all these records?

DELETE FROM t_SOWOShortTrackComments

Tim F

 

[JSand42737]

I use the following code to delete all records in a table
through VBA code. The problem is that since the table
contains over 47,000 records, it takes 3 minutes to run.
Is there a faster way to delete all these records?

Dim db As DAO.Database
Dim rstNew As DAO.Recordset

Set db = CurrentDb
Set rstNew = db.OpenRecordset("t_SOWOShortTrackComments")

With rstNew
If .RecordCount > 0 Then
.MoveFirst
Do
.Delete
.MoveNext
Loop Until .EOF
End If
End With

Joel

Rather than using a recordset, you can just execute a SQL statement:

CurrentDb.Execute "DELETE * FROM [t_SOWOShortTrackComponents];"

 

[none]

there is a quicker, easier way
dim db as dao.database

db.execute "DELETE * FROM t_SOWOShortTrackComments;"

db.close
db = Nothing