Delete Record, Access VBA Recordset

In this article I will explain how you can delete a record from an Access table using the Recordset object in VBA.

The article uses the ADODB.Recordset object. In order to use this object you may need to add reference to ADODB object library. For more information on this topic please see the link below:


Contents

How To?

The following code will initiate an ADODB.Recordset object for the table:

Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
'initated recordset obejct 
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("MyTable1", , , adLockBatchOptimistic)

Note: The code above will initiate a recordset object that will pointing at the first record of the access table.

Note: Rather than opening the entire table, we could have used a query to filter the data and return only the values we need. For example we could have replace the last line of the code above with the code below to only return values from the table whose first field has a value greater than 15:

Call objRecordset.Open ("Select MyField1, MyField2, " & _
"MyField3, MyField4 " & _
"From MyTable1 " & _
"Where MyField1 > 15 ", , , adLockBatchOptimistic)

For more information about this topic please see the link below:

Note: The first record of the table is not necessarily the first row of the table. The figure below is displaying the first row of the table:
Recordset location

By using the code below we can move the recordset pointer to the next record:

objRecordset.MoveNext

Example: The code below will loop through all the records in the recrodset:

While objRecordset.EOF = False
    objRecordset.MoveNext 
Wend 

The value of the current record the recordset object is pointing at can be read through the Fields collection. The code below will return the value in the first field of the current record the Recordset object is pointing at:

objRecordset.Fields.Item(0).Value

Note: The Fields collection of the Recordset object is a zero based collection. Therefore the first field is reference by the index “0” and the 4th field is referenced by the index “3”.

Note: The Recordset object can be moved to the previous record using the code below:

objRecordset.MovePrevious

Note: The Recordset object can be moved to the last record using the code below:

objRecordset.MoveLast

Note: The Recordset object can be moved to the first record using the code below:

objRecordset.MoveFirst


Example 1:

In this example we are going to the:

  • Step 1: Search the table for the record whose first field has the value “8”.
  • Step 2: Delete the record.

Sub Example1()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset

'initiate recordset object
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("MyTable1", , , adLockBatchOptimistic)
While objRecordset.EOF = False
    'check for a match
    If objRecordset.Fields(0).Value = 8 Then
        'delete the record
        objRecordset.Delete
        objRecordset.UpdateBatch
        'exit loop
        objRecordset.MoveLast
    End If
    'move to the next record
    objRecordset.MoveNext
Wend
End Sub

Result:
Result 3

Note: The #Deleted text will appear in place of the deleted record. One method for removing this would be to press F5. Another method would be to use userforms. I’ve explained about this topic in the article below:


Example 2:

In this example we are going to:

  • Step 1: Use a Select Query to filter the records whose first field is between “1” and “5”
  • Step 2: Delete the records.

Sub Example2()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset

'initiate recordset object
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("Select MyField1 " & _
"From MyTable1 Where MyField1 <=5 and MyField1 >=1", , , adLockBatchOptimistic)
While objRecordset.EOF = False
    'delete the record
    objRecordset.Delete
    objRecordset.UpdateBatch
    'move to the next record
    objRecordset.MoveNext
Wend
End Sub

Result:
Result 2

Note: The #Deleted text will appear in place of the deleted record. One method for removing this would be to press F5. Another method would be to use userforms. I’ve explained about this topic in the article below:

You can download the file and code used in this article from the link below:

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *