Deleting all table records through VBA / DAO 3.6

  • Thread starter Thread starter Joel Wiseheart
  • Start date Start date
J

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
 
"Joel Wiseheart" said:
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
Click to expand...

Joel

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

CurrentDb.Execute "DELETE * FROM [t_SOWOShortTrackComponents];"
 
there is a quicker, easier way
dim db as dao.database

db.execute "DELETE * FROM t_SOWOShortTrackComments;"

db.close
db = Nothing
 
Back
Top