Access VBA: How to Delete Table Rows Safely and Correctly

In Access VBA, a “row” is usually a record in a table. To delete matching records, run a DELETE query with a specific WHERE condition using CurrentDb.Execute and dbFailOnError; to delete the record currently displayed on a form, use the form’s delete command. Back up the database first: VBA deletions cannot be undone with Ctrl+Z.

What deleting a row means in Access

Access stores data in tables as records, rather than as numbered worksheet rows. A table’s displayed order is not a reliable record identifier, and a record may move in a datasheet when the sort or filter changes. For that reason, VBA should target a record by a primary key, such as an AutoNumber field named OrderID, or by another condition that uniquely identifies the intended record.

A DELETE operation removes data records. It does not remove the table itself, its fields, or its indexes. A statement without a WHERE clause deletes every record in the named table, so it deserves particular caution. Deleting a record can also affect related records if the database’s relationships are configured to cascade deletes.

Choose the method according to what the user or procedure is trying to delete:

Use a DELETE query for one record or a defined group of records identified by criteria.
Use a DAO recordset when code needs to find a record, inspect it, and delete the current record.
Use the form delete command when the target is the record currently selected in an Access form.

Delete records with a VBA DELETE query

For a straightforward deletion, build a DELETE statement and execute it through a DAO Database object. This example assumes Orders is a local Access table and OrderID is a numeric primary key. It deletes only the record whose key is 123:

Public Sub DeleteOneOrder()
    Dim db As DAO.Database
    Dim sqlText As String
    Dim orderID As Long

    ' Save a copy of the database before running a deletion.
    orderID = 123
    sqlText = "DELETE FROM Orders WHERE OrderID = " & orderID & ";"

    Set db = CurrentDb
    db.Execute sqlText, dbFailOnError

    If db.RecordsAffected = 0 Then
        MsgBox "No matching order was found."
    End If

    Set db = Nothing
End Sub

dbFailOnError asks the database engine to raise an error if the action query cannot complete, rather than quietly leaving a failed operation unnoticed. RecordsAffected reports how many records the most recent action query affected. In this example, zero means the key did not match a record; one is the expected result. If the key field is not unique, more than one record may be removed, which is why a primary key is the best target.

Use CurrentDb to get the current database, and keep the returned object in a variable when you need to inspect its result. Access VBA projects generally provide DAO; in another Office host or a non-Access project, the DAO library may need to be referenced separately. The table and field names in the example must match the actual database.

Delete a record with a DAO recordset

A DAO recordset is useful when the code must inspect a record before deciding whether to delete it. Open a dynaset filtered to the target, check that it is not at end-of-file, then call Delete on the current record. The following example again assumes a numeric primary key:

Public Sub DeleteOrderWithRecordset()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim orderID As Long
    Dim sqlText As String

    ' Save a copy of the database before running a deletion.
    orderID = 123
    sqlText = "SELECT * FROM Orders WHERE OrderID = " & orderID & ";"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(sqlText, dbOpenDynaset)

    If Not rs.EOF Then
        rs.Delete
    End If

    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

The recordset must be updateable for Delete to work. A simple query against a local table usually is, but a complex query, a read-only source, or some linked data sources may not be. If the code needs to process several matches, move through the recordset deliberately and account for the fact that deleting the current record changes its position. For a simple set-based deletion, one DELETE query is generally clearer and more efficient.

Use a parameter for text or other user-supplied criteria

Concatenating a numeric primary key, as in the first example, is simple when the value is a trusted number. Text values require more care: an apostrophe in a name can break a quoted SQL string, and careless string construction can make a condition broader than intended. A parameterized QueryDef lets Access handle the value as data instead of treating it as part of the SQL command.

This example uses the same numeric OrderID, but shows the parameter pattern. The named parameter is declared as a Long, matching the assumed field type:

Public Sub DeleteOrderWithParameter()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim orderID As Long

    ' Save a copy of the database before running a deletion.
    orderID = 123
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("", _
        "PARAMETERS pOrderID Long; " & _
        "DELETE FROM Orders WHERE OrderID = pOrderID;")

    qdf.Parameters("pOrderID") = orderID
    qdf.Execute dbFailOnError

    If qdf.RecordsAffected = 0 Then
        MsgBox "No matching order was found."
    End If

    qdf.Close
    Set qdf = Nothing
    Set db = Nothing
End Sub

For another field type, declare a parameter with the corresponding Access SQL type and assign a value of the right kind. This matters especially for dates and text. Avoid assembling date criteria by guessing how a user’s regional settings format dates; parameter values sidestep many formatting problems. Ensure the parameter name and declared type agree with the value and the field being compared.

Delete the current record from a form

If a button on a bound form should delete the record currently shown, Access provides a built-in command. In the form’s button click event, this runs the delete-record command:

Private Sub cmdDelete_Click()
    ' Save a copy of the database before running a deletion.
    DoCmd.RunCommand acCmdDeleteRecord
End Sub

This command acts on the active form’s current record, not on an arbitrary table row selected by a key in another control. It can invoke Access’s normal delete behavior, including confirmation prompts and form events. If the form is not positioned on a deletable record, the command can fail; handle that possibility in a finished interface and explain clearly to the user which record will be removed.

A form may also have its own delete permissions, and its record source may be non-updateable. A command that works on a simple form bound to one table may not work on a form based on a complex query. If the intent is instead to delete a record selected by an ID value, use a parameterized DELETE query and verify that the ID is valid; do not assume the displayed record is necessarily the one identified in a separate control.

Check criteria, relationships, and delete scope

The WHERE clause is the boundary between a targeted deletion and a mass deletion. For example, DELETE FROM Orders WHERE OrderID = 123; targets one key, while DELETE FROM Orders WHERE Status = 'Cancelled'; removes every order that meets that condition. The latter may be appropriate, but first confirm how many records match. A missing or incorrect condition can be destructive.

Use a primary key condition when removing one record.
For a group deletion, check the criteria with a SELECT query first and count or review the matching records.
Test against a copy of the database before using the procedure on production data.
Consider displaying a confirmation that includes the number of records to be deleted.

Relationships can change the scope. If referential integrity is enforced and cascade delete is enabled for a relationship, deleting a parent record may also delete related child records. Without cascade delete, Access may block deletion of a parent that still has related records. Review the Relationships window and test the actual configuration rather than assuming that a parent deletion affects only one table. A DELETE query against one table should not be treated as a substitute for understanding those relationship rules.

Common errors and safe troubleshooting

A “could not delete” error can have several causes. The record may be locked by another user, the record source may be read-only, a relationship may prevent the deletion, or the user may lack permission on a linked data source. Check the exact error and the table or query involved; changing the WHERE clause will not fix a permissions or relationship problem.

If a query deletes too many records, inspect the final SQL string and verify that the WHERE clause is present and correctly formed before execution. During development, you can print the SQL to the Immediate window with Debug.Print sqlText and examine it before running the action. Do not leave an unfiltered DELETE statement in code that was intended to delete one record. When deletion is based on user input, validate the input and prefer parameters.

Access does not provide the spreadsheet-style Ctrl+Z undo for VBA action queries. A transaction can help group supported database operations so they can be committed or rolled back, but not every external or linked data source supports transactions in the same way. Do not rely on a transaction as a replacement for a backup. For a routine with several related changes, use transactions only after checking that the target data source supports them and that error handling rolls back incomplete work.

DELETE query versus DAO and form commands

These approaches are not interchangeable in every situation. A DELETE query is concise for removing records that meet a condition and is usually the natural choice for batch work. A DAO recordset gives code a current record to examine, making it useful when each candidate needs a decision or additional processing. A form command follows the user-interface context and deletes the currently selected form record.

For a permanent cleanup routine, favor a query with explicit criteria and a clear result check. For an interactive screen, a form command may better preserve the form’s normal events and prompts. For specialized logic, such as deciding based on values in each record, a recordset may be easier to follow. In every case, identify the exact target, consider related records, and provide a recovery plan before running code against important data.

Frequently Asked Questions

How do I delete all rows from an Access table with VBA?
Use a DELETE query without a WHERE clause, such as DELETE FROM TempImport;, but only when removing every record is intentional. It leaves the table and its structure in place. Verify the table name, back up the database, and consider confirming the intended record count before running it.

How do I delete one row by ID in Access VBA?
Use a DELETE statement with a WHERE condition on the primary key, such as DELETE FROM Orders WHERE OrderID = 123;. In production code, pass the ID as a parameter and check RecordsAffected so you can detect whether no record matched.

Why does my Access VBA delete query remove nothing?
The criteria may not match the stored value, the field type may not match the parameter type, or the target may be a linked or read-only source. Check the record with a SELECT query using the same criteria, then confirm the field name, data type, permissions, and query result.

Can I undo a row deletion in Access VBA?
Not with Ctrl+Z after a VBA action query has run. Restore from a backup or another recovery source if needed. For that reason, test deletion code on a copy and use explicit criteria, confirmation, and error handling before deployment.

Does deleting a record also delete related records?
It can, when cascade delete is enabled for the relationship. If cascade delete is off, Access may refuse to delete a parent record that still has related children. Check the relationship settings and test the effect on a copy of the database.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.