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 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.
Add Student ID to the field, then type “001” in criteria, which is the student ID of Apple.
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 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 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 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