4.3.5 Working with Recordsets: Deleting Records

In Access VBA, deleting records from a Recordset can be achieved using the Delete method. Once you find the record you want to delete, you can use this method to remove the record from the underlying table or query.

This section will guide you through the process of deleting records using VBA, including the necessary steps, syntax, and examples.

1. Basic Steps to Delete Records

To delete a record from a Recordset in Access, follow these general steps:

1. Open a Recordset: Open the Recordset for the table or query from which you want to delete records.
2. Locate the Record: Use methods like FindFirst, FindNext, or loop through the records to find the record(s) you wish to delete.
3. Delete the Record: After locating the record, use the Delete method to remove it.
4. Call the Update Method: After deleting the record, call the Update method to reflect the changes in the database.
5. Close the Recordset: Close the Recordset after completing the deletion process.

2. Using the Delete Method

The Delete method removes the current record from the Recordset. It does not move to the next record, so you need to make sure that the correct record is selected before calling Delete.

Key Syntax:

Recordset.Delete

• Delete: Deletes the current record from the Recordset.

3. Example of Deleting a Record

In this example, we will delete a record from the Employees table where the EmployeeName is "John Doe".

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Employees", dbOpenDynaset) ' Open the table in Dynaset mode

' Find the record for "John Doe"
rs.FindFirst "EmployeeName = 'John Doe'"

If Not rs.NoMatch Then
    ' Delete the record
    rs.Delete
    MsgBox "Record deleted successfully"
Else
    MsgBox "Record not found"
End If

' Close the Recordset
rs.Close
Set rs = Nothing
Set db = Nothing

Explanation:

1. We open the Employees table in Dynaset mode to allow editing and deleting records.
2. We use FindFirst to locate the record where EmployeeName = 'John Doe'.
3. If the record is found (Not rs.NoMatch), we use the Delete method to remove it from the Recordset.
4. We display a message to confirm the deletion, and after that, the Recordset is closed.

4. Deleting Multiple Records in a Loop

If you need to delete multiple records, you can loop through the Recordset and apply the Delete method to each matching record. However, be careful when deleting records in a loop, as deleting a record will cause the cursor to move to the next record automatically.

Example of Deleting Multiple Records:

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Employees", dbOpenDynaset) ' Open table in Dynaset mode

' Loop through the records
Do While Not rs.EOF
    ' If the employee is in the "IT" department and their salary is below 50000, delete the record
    If rs!Department = "IT" And rs!Salary < 50000 Then
        rs.Delete
    Else
        rs.MoveNext
    End If
Loop

' Close the Recordset
rs.Close
Set rs = Nothing
Set db = Nothing

Explanation:

1. We open the Employees table in Dynaset mode.
2. We loop through each record and check if the Department is "IT" and the Salary is below 50000.
3. If the condition is met, the Delete method is called to remove the record from the Recordset.
4. After deleting, the loop continues with the next record. If the record is not deleted, we move to the next record using MoveNext.

5. Error Handling for Deleting Records

When deleting records, it’s important to implement error handling to avoid unexpected issues, such as trying to delete a record that no longer exists or working with an empty Recordset.

Example with Error Handling:

Dim db As Database
Dim rs As Recordset

On Error GoTo ErrorHandler ' Set up error handling

Set db = CurrentDb
Set rs = db.OpenRecordset("Employees", dbOpenDynaset)

' Find the record for "John Doe"
rs.FindFirst "EmployeeName = 'John Doe'"

If Not rs.NoMatch Then
    rs.Delete
    MsgBox "Record deleted successfully"
Else
    MsgBox "Record not found"
End If

' Close the Recordset
rs.Close
Set rs = Nothing
Set db = Nothing

Exit Sub

ErrorHandler:
    MsgBox "Error: " & Err.Description
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Explanation:

• The On Error GoTo ErrorHandler directs the program to the ErrorHandler section if an error occurs.
• If an error occurs (e.g., due to a missing record or other issues), the error message will display the error description, and the Recordset will be properly closed.

6. Deleting Records with a SQL Query

Instead of using Recordsets to delete records, you can also delete records using an SQL DELETE query in VBA. This approach is generally faster when you need to delete many records.

Example Using SQL Query:

Dim db As Database

Set db = CurrentDb

' Delete all employees from the IT department with salary below 50000
db.Execute "DELETE FROM Employees WHERE Department = 'IT' AND Salary < 50000", dbFailOnError

MsgBox "Records deleted successfully"

Set db = Nothing

Explanation:

• We use the Execute method to run a SQL DELETE query directly.
• The WHERE clause specifies the condition to match records for deletion (e.g., employees in the "IT" department with a salary below 50000).
• The dbFailOnError option ensures that if an error occurs, the operation is halted, and the error is raised.

7. Conclusion

Deleting records from a Recordset in Access VBA is simple and involves opening the Recordset, locating the record, and using the Delete method. For deleting multiple records, loops can be employed, but care should be taken with cursor movement after deletion. It's also important to implement error handling to prevent issues during deletion. Additionally, SQL queries can be an efficient way to delete records in bulk.