Access VBA delete Table records with SQL using DoCMD.RunSQL Method

This Access VBA tutorial explains how to use VBA delete Table records with SQL without alert using DoCMD.RunSQL Method.

You may also want to read:

Access VBA run Query or run Action Query

Access delete Table records

In my previous post, I have explained how to delete Table records without using VBA, below is a quick recap.

Suppose we have created a table called student in Access, which contains 5 student records.

student
Student ID Student Name
001 Apple
002 Betty
003 Cathy
004 David
005 Elyse

In order to delete the record (the whole row) of Apple, create a new Query, add student table.

Under Design tab, click on Delete button. This will create a Delete Query.

access-delete-table-records-01

 

Add Student ID to the field, then type “001” in criteria, which is the student ID of Apple.

access-delete-table-records-02

 

To preview the result of Delete Query (which records will be deleted), click on the View button under Design.

To execute the Query (actually delete the records), click on Run button, then click on Yes to confirm delete row.

access-delete-table-records-03

 

Access VBA delete Table records

Similar to executing Delete Query in Access UI, you can delete Access Table records in VBA. First write a standard Delete SQL statement, then use DoCMD.RunSQL Method to execute the SQL.

For example, we have a student Table as below.

student
Student ID Student Name
001 Apple
002 Betty
003 Cathy
004 David
005 Elyse

 

Press ALT+F11 and insert the below code in Module.

Public Sub deleteRecord()
    DoCmd.SetWarnings False
    strSQL = "Delete * From [student] WHERE [Student ID]='002'"
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
End Sub

Writing DoCmd.SetWarings False is because we want to avoid Access generating the below Alert so that the subsequent statements can be executed smoothly.

 

Run the Sub, then you will see the below result where Student ID 002 is deleted.

student
Student ID Student Name
001 Apple
003 Cathy
004 David
005 Elyse

 

Outbound References

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.runsql

Wyman W
Wyman is human resources professional specialized in implementation of HR information system.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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