Fast Delete All

[John Marshall, MVP]

Is there a faster method of deleting all records than:

Set rst = CurrentDb.OpenRecordset("CnvAddress")
For i = 1 To rst.RecordCount
rst.Delete
rst.MoveNext
Next i
rst.Close
Set rst = Nothing

I'm testing conversion routines and I need to empty the tables before
refreshing them.

John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm

 

[Douglas J. Steele]

CurrentDb.Execute "DELETE * FROM CnvAddress", dbFailOnError

It's almost always significantly faster to use SQL rather than looping
through a recordset.

 

[John Marshall, MVP]

Thanks Doug. Since the record deletion required moving to the next record,
there had to be a faster solution.

John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm

 

[John Marshall, MVP]

It's a lot faster.


John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm