Delete all records in a table

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

Guest

I tried two different methods of deleting all records in a table, while
preserving the TableDef:

Method 1:
With DoCmd
.OpenTable "tblTablename"
.RunCommand acCmdSelectAllRecords
.RunCommand acCmdDeleteRecord
.Close
End With

Method 2:
Dim rst as DAO.Recordset
Set rst = db.OpenRecordset("tblTablename")

With rst
Do Until .EOF
.Delete
.MoveNext
Loop
.Close
End With

Method 1 is fast, but it flashes the table itself on the screen. Method 2 is
slow, since it has to delete the records one at a time.

Is there a faster way to delete all the records in a table, without anything
displaying on the screen?

Thanks!
 
Bill said:
I tried two different methods of deleting all records in a table, while
preserving the TableDef:

Method 1:
With DoCmd
.OpenTable "tblTablename"
.RunCommand acCmdSelectAllRecords
.RunCommand acCmdDeleteRecord
.Close
End With

Method 2:
Dim rst as DAO.Recordset
Set rst = db.OpenRecordset("tblTablename")

With rst
Do Until .EOF
.Delete
.MoveNext
Loop
.Close
End With

Method 1 is fast, but it flashes the table itself on the screen. Method 2 is
slow, since it has to delete the records one at a time.

Is there a faster way to delete all the records in a table, without anything
displaying on the screen?
Click to expand...


In almost all situations, the preferred way is:

db.Execute "DELETE * FROM tblTablename"
 
Back
Top