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 code 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. Be aware 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.