How to: Delete a Record From a DAO Recordset

You can delete an existing record in a table or dynaset-type Recordset object by using the Delete method. You cannot delete records from a snapshot-type Recordset object. The following example deletes all the duplicate records in the Shippers table:

  Sub DeleteDuplicateShippers()
Dim dbsNorthwind As DAO.Database
Dim rstShippers As DAO.Recordset
Dim strSQL As String
Dim strName As String
On Error GoTo ErrorHandler
Set dbsNorthwind = CurrentDb
strSQL = "SELECT * FROM Shippers ORDER BY CompanyName, ShipperID"
Set rstShippers = dbsNorthwind.OpenRecordset(strSQL, dbOpenDynaset)
'If no records in Shippers table, exit.
If rstShippers.EOF Then Exit Sub
strName = rstShippers![CompanyName]
rstShippers.MoveNext
Do Until rstShippers.EOF
If rstShippers![CompanyName] = strName Then
rstShippers.Delete
Else
strName = rstShippers![CompanyName]
End If
rstShippers.MoveNext
Loop
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

When you use the Delete method, the Microsoft Access database engine immediately deletes the current record without any warning or prompting. Deleting a record does not automatically cause the next record to become the current record; to move to the next record you must use the MoveNext method. However, keep in mind that after you have moved off the deleted record, you cannot move back to it.

If you try to access a record after deleting it on a table-type Recordset, you will see error 3167, "Record is deleted." On a dynaset, you will see error 3021, "No current record."

If you have a Recordset clone positioned at the deleted record and you try to read its value, you will see error 3167 regardless of the type of Recordset object. Trying to use a bookmark to move to a deleted record will also result in error 3167.