Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.
Anytime you want to either rapidly erase much data or eliminate a set of data frequently in an Access desktop database, a delete or an update query may be helpful because the queries open up the possibility of defining criteria to easily discover and erase the data. Applying a query can even be a timesaver because you can reapply a saved query.
Note: Before you delete any data or run a delete query, make sure that you have a backup of your Access desktop database.
If you want to only delete a few records, you don’t need a query. Simply open the table in Datasheet View, choose the fields (columns) or records (rows) that you want to delete, and then press DELETE.
Important: The information in this article is intended for use only with desktop databases. You cannot use delete or update queries in Access web apps.
In this article
Choosing a query type
You can use either an update query or a delete query to delete data from your database. Pick a query linked to the details in the following table:
|Type of query||When to use it||Results|
|Use a delete query||To remove entire records (rows) from a table or from two related tables simultaneously.|
Note: If the records reside on the “one” side of a one-to-many relationship, you might need to change the relationship before you run the delete query. See the section on deleting data from related tables.
|Delete queries remove all the data in each field, including the key value that makes a record unique|
|Use an update query||To delete individual field values from a table.||Makes it easier to delete values by updating the existing values to either a null value (that is, no data) or a zero-length string (a pair of double quotation marks with no space between them).|
Things to verify before you use a query to delete any data
- Make sure that the file is not a read-only file:
a. Right-click Start and select Open Windows Explorer.
b. Right-click the database file and choose Properties.
c. See if the Read-only attribute is chosen.
- Verify that you have the necessary permissions to delete records from the database. If you are not sure, contact your system administrator or the database designer.
- Make sure that you have enabled content in the database. By default, Access blocks all action queries (delete, update, and make-table queries) unless you first trust the database. For information about trusting a database, see the section Stop Disabled Mode from blocking a query.
- Ask other users of the database to close all tables, forms, queries, and reports that use the data that you want to delete. This helps avoid lock violations.
- Before you edit or delete records, making a backup copy of the database is a good idea in case you want to reverse your changes.
Tip: If a large number of users connect to the database, you might need to close the database and then reopen it in Exclusive mode.
To open a database in Exclusive mode
- Select File tab > Open.
- Browse to and point to choose the database, select the arrow beside the Open button, and then pick Open Exclusive.
Back up the database
- Pick the File tab, point to Save As.
- Select Save Database As, click Back Up Database. Access closes the original file, creates a backup, and then reopens the original file.
- Choose Save As and specify a name and location for the backup copy, and press Save.
Note: If you are using a read-only or a database created in the previous version of Access, you might get a message that it is not possible to create a back-up of the database.
To revert to a backup, close and rename the original file so that the backup copy can use the name of the original version. Assign the name of the original version to the backup copy, and open the renamed backup copy in Access.
Using a delete query
To create a delete query, click the Create tab, in the Queries group, click Query Design. In the Show Table dialogue box, double-click each table from which you want to delete records, and then click Close.
The table appears as a window in the upper section of the query design grid. From the list of fields, double-click the asterisk (*) to add all of the fields in the table to the design grid.
Using a specific criteria in a delete query
Important: Use criteria to return only the records that you want to delete. Otherwise, the delete query removes every record in the table.
Double-click the field that you want to specify as the criteria for deletion, enter one the criteria in the Criteria row of the query designer, and then clear the Show check box for each criteria field.
An example of when you might want to use this option: Suppose you want to remove all of the pending orders for a customer. To find just those records, you’d add the Customer ID and Order Date fields to the query design grid, and then you enter the ID number of the customer, and the date on which that customer’s orders became invalid.
- On the Design tab, choose View > Datasheet View.
- Ascertain that the query returns the records that you want to delete, and then press CTRL+S to save the query.
- To process the query, double-click the query in the Navigation Pane.
Using an update query
Note: You cannot use the update query option in an Access web app.
This section explains how to use an update query to delete individual fields from tables. Remember that running an update query to delete data will change the existing values to either NULL or a zero-length string (a pair of double quotation marks with no space in between), depending on the criteria that you specify.
- Press the Create tab and in the Queries group, click Query Design.
- Pick the table which has the data you want to delete (if the table is related, select the table on the “one” side of the relationship), click Add, and then select Close. The table displays as a window in the upper section of the query design grid. The window lists all of the fields in the selected table.
- Double-click the asterisk (*) to append all of the fields in the table to the design grid. Adding all the table fields enables the delete query to remove entire records (rows) from the table. Optionally, you can type criteria for one or more fields in the Criteria row of the designer, and then empty the Show checkbox for each criteria field. For more information about using criteria, see the Sample criteria for select queries table.
Note: You use criteria to return only the records that you want to change. Otherwise, the update query sets to NULL every record in each of the fields in your query.
- On the Design tab, in the Results group, pick View, and then choose Datasheet View.
- Confirm that the query returns your desired records to set to NULL or a zero-length string (a pair of double-quotation marks with no space between them (“”).
- As required, repeat steps 3 to 5 and edit the fields or criteria until the query returns only your preferred data to erase, and then press CTRL+S to save the query.
- To run the query, double-click the query in the Navigation Pane.
Deleting data from related tables
If you want to delete data from several related tables, you must enable the Referential Integrity and Cascade Delete Related Records options for each relationship. This allows your query to delete data from the tables on the “one” and “many” sides of the relationship. Preparing to deleted related data requires verification of the following:
- Determine which records reside on the “one” side of the relationship and which reside on the “many” side.
- If you need to delete records on the “one” side of the relationship and the related records on the “many” side, you enable a set of rules called Referential Integrity, and you enable cascading deletes. Steps in this section explain Referential Integrity, and how to perform both tasks.
- If you need to delete records only on the “one” side of the relationship, you first delete that relationship, and then delete the data.
If you need to remove data only on the “many” side of the relationship, you can create and run your delete query without having to change the relationship.
To find out which records reside on the “one” and “many” sides of a relationship, on the Database Tools tab, in the Relationships group, click Relationships. The Relationships tab displays the tables in your database and the relationships. Each relationship is depicted as a line that connects tables between fields.
The following figure shows a typical relationship. Most, if not all, of the relationships in a database have a “one” side and a “many” side. The relationship diagram denotes the “one” side with the numeral one (1) and the “many” side with the infinity (∞) symbol.
When you delete records on the “one” side of the relationship, you also delete all of the related records on the “many” side of the relationship. However, when you delete records on the “many” side of a relationship, you typically don’t delete records on the “one” side.
Also, Access automatically enforces a set of rules called referential integrity. These rules ensure that the foreign keys in a database contain the correct values. A foreign key is a column whose values match the values in the primary key column of another table.
Edit the relationship
Follow these steps only when you need to delete data on the “one” and “many” sides of a relationship.
- On the Database Tools tab, in the Relationships group, choose Relationships.
- Right-click the relationship (the line) linking the tables within the deletion operation, and then select Edit Relationship on the shortcut menu.
- In the Edit Relationships dialogue box, make sure that the Enforce Referential Integrity checkbox is chosen.
- Select the Cascade Delete Related Records checkbox.
Note: Until you disable this property again, deleting a record on the “one” side of the relationship will delete all of the related records on the “many” side of the relationship.
- Press OK, exit the Relationships pane, and then go proceed to the following set of steps.
Delete a relationship
- If you still need to do so, open the Relationships pane.
- On the Database Tools tab, in the Relationships group, pick Relationships. Record the fields within the relationship to allow you to restore the relationship following you erasing your data.
- Right-click the relationship (the line) joining the tables contained in the deletion operation, and then press Delete on the shortcut menu.
Note: To restore the relationship, follow the previous steps to open the Relationships pane, and then drag the primary key field from the “one” table and drop it on the foreign key field of the “many” table. The Edit Relationship dialogue box appears. If the old relationship enforced referential integrity, select Enforce Referential Integrity, and then click Create. Otherwise, just click Create.
Sample criteria for select queries
The following table lists some sample criteria that you can use in select queries when you want to make sure that you delete only the data that you want deleted. Some of these examples use wildcard characters.
|> 234||Returns all numbers greater than 234. To find all numbers less than 234, use < 234.|
|>= “Cajhen”||Returns all records from Cajhen through the end of the alphabet.|
|Between #2/2/2010# And #12/1/2010#||Returns dates from 2-Feb-2010 through 1-Dec-2010 (ANSI-89). If your database uses the ANSI-92 wildcard characters, use single quotation marks (‘) instead of pound signs (#). Example: Between ‘2/2/2010′ And ’12/1/2010’.|
|Not “Germany”||Finds all records where the exact contents of the field are not exactly equal to “Germany.” The criterion will return records that contain characters in addition to “Germany,” such as “Germany (euro)” or “Europe (Germany)”.|
|Not “T*”||Finds all records except those beginning with T. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).|
|Not “*t”||Finds all records that do not end with t. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).|
|In(Canada,UK)||In a list, finds all records containing Canada or UK.|
|Like “[A-D]*”||In a Text field, finds all records that start with the letters A through D. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).|
|Like “*ar*”||Finds all records that include the letter sequence “ar”. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).|
|Like “Maison Dewe?”||Finds all records that begin with “Maison” and contain a 5-letter second string in which the first 4 letters are “Dewe” and the last letter is unknown. If your database uses the ANSI-92 wildcard character set, use the underscore (_) instead of the question mark (?).|
|#2/2/2010#||Finds all records for February 2, 2010. If your database uses the ANSI-92 wildcard character set, surround the date with single quotation marks instead of pound signs (‘2/2/2010’).|
|< Date() – 30||Uses the Date function to return all dates more than 30 days old.|
|Date()||Uses the Date function to return all records containing today’s date.|
|Between Date() And DateAdd(“M”, 3, Date())||Uses the Date and the DateAdd functions to return all records between today’s date and three months from today’s date.|
|Is Null||Returns all records that contain a null (blank or undefined) value.|
|Is Not Null||Returns all records that contain any value (that are not null).|
|“”||Returns all records that contain a zero-length string. You use zero-length strings when you need to add a value to a required field, but you don’t yet know what the actual value is. For example, a field might require a fax number, but some of your customers might not have fax machines. In that case, instead of entering a number, you enter a pair of double quotation marks with no space between them (“”).|
Why am I seeing this error message, and how do I fix it?
If you construct a delete query by using multiple tables and the query’s Unique Records property is set to No, Access presents the error message; Could not delete from the specified tables once you run the query.
To fix this problem, set the query’s Unique Records property to Yes.
- Enter the delete query in Design View.
- If the query property sheet is closed, press F4 to bring it up.
- Choose the query designer to show the query properties (rather than the field properties).
- In the query property sheet, find the Unique Records property, and switch it to Yes.
Stop Disabled Mode from blocking a query
By default, if you open a desktop database that you have not chosen to trust or that does not reside in a trusted location, Access blocks all action queries from running.
If you seek to run an action query and it seems like nothing happens, check the Access status bar for the following message:
This action or event has been blocked by Disabled Mode.
After you see that message, take the following step to allow the blocked content:
- On the Security Warning Message Bar, pick Enable Content, and run your query again.