Access Action Queries: Update, Delete, Append, and Make-Table

Most Access queries are SELECT queries — they retrieve and display data without changing anything. Action queries are different: they modify data. A single action query can update thousands of records, delete an entire year of old data, or copy records from one table to another — all in seconds.

With that power comes responsibility. An action query that runs incorrectly can destroy data. This guide covers all four types of action queries and the safety practices that prevent disasters.

The Golden Rule: Always Preview First

Before running any action query, convert it to a SELECT query first to preview exactly which records will be affected. Change UPDATE to SELECT *, or DELETE to SELECT *, run it, and verify the results. Only then switch back to the action query and run it.

Also: back up your database before running any action query on production data.

Update Queries

An Update query changes field values in existing records. It is the equivalent of running UPDATE ... SET ... WHERE in SQL.

Example: Give all customers in California a 10% discount:

UPDATE Customers
SET DiscountRate = DiscountRate * 1.1
WHERE State = "CA";

In the Query Designer:

1. Create a new query and add your table
2. Go to Query → Update Query (or click the Update button on the Design tab)
3. Add the fields you want to update to the grid
4. In the Update To row, enter the new value or expression
5. Add criteria in the Criteria row to limit which records are updated
6. Click Run (the red exclamation mark)

Access will warn you how many records will be updated. Click Yes to proceed.

Common Update Query uses:

• Standardize data (convert all state abbreviations to uppercase)
• Apply price increases across a product category
• Mark records as processed after a batch operation
• Set a default value for a new field across all existing records

Delete Queries

A Delete query permanently removes records from a table. There is no undo — deleted records are gone.

Example: Delete all orders older than three years:

DELETE FROM Orders
WHERE OrderDate < DateAdd("yyyy", -3, Date());

Safety checklist before running a Delete query:

1. Preview with SELECT * first — verify the exact records that will be deleted
2. Back up the database
3. Check for related records in other tables — if referential integrity is enforced, you may need to delete child records first
4. Consider archiving instead of deleting — run an Append query to copy records to an archive table, then delete

Cascade deletes: If you have referential integrity with cascade deletes enabled, deleting a parent record automatically deletes all related child records. Be aware of this when writing Delete queries against parent tables.

Append Queries

An Append query copies records from one table (or query result) and adds them to another table. The destination table must already exist and have compatible fields.

Example: Archive orders older than three years to an OrdersArchive table:

INSERT INTO OrdersArchive
SELECT *
FROM Orders
WHERE OrderDate < DateAdd("yyyy", -3, Date());

In the Query Designer:

1. Create a new query with the source table
2. Go to Query → Append Query
3. Select the destination table
4. Map the source fields to the destination fields
5. Add criteria to filter which records to append
6. Run

Common Append Query uses:

• Archiving old records before deleting them
• Combining data from multiple tables into one
• Copying template records to create new entries
• Importing data from a staging table into the main table

Make-Table Queries

A Make-Table query creates a brand new table from the results of a SELECT query. It is like a SELECT INTO statement in other SQL dialects.

Example: Create a snapshot table of all active customers for a mailing:

SELECT CustomerID, FirstName, LastName, Email
INTO MailingList_September2026
FROM Customers
WHERE Status = "Active";

In the Query Designer:

1. Create a SELECT query with the fields you want
2. Go to Query → Make Table Query
3. Enter a name for the new table
4. Run

Common Make-Table Query uses:

• Creating snapshot tables for reporting at a point in time
• Building temporary working tables for complex multi-step operations
• Exporting a subset of data to a new database
• Creating denormalized tables for performance-intensive reports

Note: Running a Make-Table query on an existing table name will delete and recreate the table. Access warns you, but be careful.

Running Action Queries from VBA

You can run action queries programmatically using DoCmd.RunSQL or CurrentDb.Execute:

' Using DoCmd.RunSQL (shows warnings by default)
DoCmd.RunSQL "UPDATE Products SET Price = Price * 1.05 WHERE Category = 'Electronics'"

' Using CurrentDb.Execute (no warnings, faster)
CurrentDb.Execute "UPDATE Products SET Price = Price * 1.05 WHERE Category = 'Electronics'", dbFailOnError

' Check how many records were affected
Debug.Print CurrentDb.RecordsAffected & " records updated"

Use dbFailOnError with CurrentDb.Execute to ensure errors are raised as VBA errors rather than silently ignored.

Suppressing Action Query Warnings

When running action queries from VBA, Access displays confirmation dialogs by default. Suppress them with:

DoCmd.SetWarnings False
' Run your action queries here
DoCmd.SetWarnings True  ' Always re-enable!

Always re-enable warnings after your queries run. Leaving warnings disabled can cause silent failures in other parts of your application.

Conclusion

Action queries are among the most powerful tools in Access. They let you perform bulk data operations that would take hours to do manually, in seconds. The key is discipline: always preview before running, always back up first, and always test on a copy of the data before running on production. Build these habits and action queries will become one of your most reliable tools.