Deleting ALL records using DAO method (problem)???

  • Thread starter Thread starter Niklas Östergren
  • Start date Start date
N

Niklas Östergren

Hi!

How do I do to delete all records in a local table using DAO?

Her´s the code I use now but it only delets the first record. I still have
records left:

=============================================
Dim db As Database
Dim rst As Recordset

Set db = Currentdb
Set rst = db.OpenRecordset(strTableName)

rst.Delete

rst.Close
=============================================

TIA
// Niklas
 
Execute a Delete query statement:
Set db = CurrentDb()
db.Execute "DELETE FROM MyTable;", dbFailOnError
 
DAO recordsets typically work with one record at a time. If you wanted
to do deletions this way, you'd have to use something like this:


Dim db As Database
Dim rst As Recordset

Set db = Currentdb
Set rst = db.OpenRecordset(strTableName)

Do While Not rst.EOF
rst.Delete
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing

However, it's often more efficient to just have the DB engine operate
on records in batch:

Public Sub DeleteAll(strTbl as String)
On Error Goto ErrHandler
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM " & strTbl)
ExitHere:
DoCmd.SetWarnings True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHere
End Sub

This avoids the overhead of declaring 2 object variables and stepping
through a recordset. Also, you can sidestep a lot of complicated and
error-prone coding by using this approach.

Of course, if you wanted a confirmation of the delete to appear, leave
out the DoCmd.SetWarnings False stuff.

Hope this helps.
 
Back
Top