Delete one or more records from an Access database

Applies to
Microsoft Office Access 2003

Removing unneeded data is a good housekeeping strategy that all users of a database should practice. Weeding out obsolete or incorrect data leaves a database easy to use and maintain.

Data becomes unnecessary due to several reasons:

  • A change in the real world. Examples: Discontinuing a product or closing down business in a specific region.
  • A user mistake. Examples: Entering duplicate data for a customer or creating multiple forms to enter invoice data.
  • A lapse of time. Example: Moving the previous quarter's data to an archive database at the start of each quarter.

The design of your database and the factors just mentioned determine what you need to delete from the database. It might be as simple as removing the contents of a field or as complex as deleting multiple tables.

Important    If you plan to do more than just empty a field, first familiarize yourself with the design of your database. Adding or editing a record rarely affects related records, but deleting a record often requires that you either edit or delete related records. For example, if you decide to delete a Category record from the Categories table in the Northwind Traders sample database, you should delete records from several related tables, such as Products (all the products that belong to this category) or Orders Details (parts of orders that correspond to the products that belong to this category).

If you didn't design or create the database, spend time reviewing its design. You should be familiar with the structure of the tables in the database, the relationships between the tables, and the various objects that are dependent on the tables so that you understand the databasewide implications of deleting a record or an object.

This topic describes the various ways in which you can permanently delete one or more records from a database. If you want to temporarily remove records from your view, you can apply a filter to hide the records that you are not interested in. For more information on filters, see the topic Filter: Limit the number of records in a view or report.

Implications of deleting a record

The most important thing to remember when you delete a record is that it might result in the deletion of several records in related tables. You might wonder why deleting a record often affects related records. The answer is simple, but might not be obvious — a database stores related information in multiple tables, so when you remove a piece of critical information from a table, related information that is stored in other tables might need to be modified or removed.

Let's take an example to understand why deleting related records is a good thing. Consider a scenario where you need to add a record to the Northwind.mdb sample database. The company finds a new customer named Wide World Importers, so you add a new record for this customer to the Customers table.

Customers table

To see if related tables will be affected by this new record, identify the tables that are related to the Customers table. Right-click the background of the Database window, and click Relationships to display the Relationships window.

Tables that are related to Customers table

The Customers table is related to the Orders table, but there is no need to add any records to the Orders table until the customer places an order. So adding a record to one table does not necessarily force you to add data to related tables.

Later, a customer goes out of business. Now you need to delete the customer record. In addition, you need to cancel this customer's pending orders. Let's assume you also have no need for the data that tracks the closed orders that were placed by this customer. There might be cases where you might not want to delete past orders, but to keep it simple, let's assume you want to. Deleting this customer implies deleting some related records from the Orders table. The following illustration shows the records from the Orders that must be deleted along with the customer record.

Orders records that must be deleted

The task doesn't stop at just deleting the related order records. You need to assess the impact of deleting order records on the tables that are related to the Orders table. Go back to the Relationships window (right-click the background of the Database window and click Relationships to display the window), and identify the tables that are related to the Orders table.

Tables that are related to Orders table

The Customers, Shippers, OrderDetails, and Employees tables are all related to the Orders table. Simply deleting an order should never involve deleting the corresponding customer record, shipper record, or employee record. On the other hand, details of an order should be removed if the order no longer exists. So you need to make sure that when an order record is deleted from the Orders table, the corresponding order detail records are deleted from the OrderDetails table.

In the following illustration, each subdatasheet shows the order detail records that correspond to each order record that needs to be deleted.

Order details records that must be deleted along with orders and customer record

To summarize, deleting a single customer record makes it necessary to delete several records from the Orders and Order Details tables. At the same time, deleting records from the Orders table does not require deleting any records from the Shippers and Employees tables.

Let's see how you enable automatic deletion of records in the Orders and Order Details tables when you delete a customer record.

In the Relationships window, click the middle segment of the line that connects the Customers and Orders tables to open the Edit Relationships window.

Edit Relationships dialog box

The tables are related, based on the CustomerID field that exists in both tables. The Enforce Referential Integrity and Cascade Update Related Fields check boxes are already selected, but the Cascade Delete Related Records check box is not selected.

The first check box enforces referential integrity. Referential integrity is a set of rules that ensure that the database remains in sync with the real world, as explained here:

  • Rule 1: You can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table. However, you can enter a Null value in the foreign key, specifying that the records are unrelated. For example, you can't have an order that is assigned to a customer that doesn't exist, but you can have an order that is assigned to no salesperson — you do this by entering a Null value in the Employee field.
  • Rule 2: You can't delete a record from a primary table if matching records exist in a related table. For example, you can't delete an employee record from the Employees table if there are orders assigned to the employee in the Orders table.
  • Rule 3: You can't change a primary key value in the primary table, if that record has related records. For example, you can't change a customer's ID in the Customers table if there are orders placed by that Customer in the Orders table.

The rules are enforced each time you add, edit, or delete data in the database. When an action violates a rule, Access displays a message and cancels the action. For example, try to delete the Wide World Importers customer record from the Customers table. The relationship between the Customers and Orders tables enforces referential integrity, and the following message appears if you try to delete a record from the Customers table:

Message that you cannot delete Customers record

Referential integrity can be enforced in a relationship when the following conditions are met:

  • The matching field (in this case, CustomerID) from the primary table (Customers) is a primary key or has a unique index.
  • The related fields (CustomerID) have the same data type.

 Note   There are two exceptions to this rule. An AutoNumber field can be related to a Number field with a FieldSize property setting of Long Integer, and an AutoNumber field with a FieldSize property setting of Replication ID can be related to a Number field with a FieldSize property setting of Replication ID.

  • Both tables belong to the same Access database.

 Note   If the tables are linked, they must be in Access format. You must open the database in which they are stored to set referential integrity. Referential integrity can't be enforced for linked tables from databases in other formats.

When referential integrity is enabled in a relationship between two tables, you can optionally enable automatic updates or deletion of records in those tables. If you select these two options, it's important to note that delete and update operations that would normally be prevented by referential integrity rules are allowed. For example, when the Cascade Delete Related Records setting is not selected, Access does not allow you to delete a customer record as long as there are records in the Orders table that reference this customer's ID. Selecting the Cascade Delete Related Records check box will change this behavior. When you try to delete a customer record after you select this check box, Access automatically deletes the related records instead of displaying an error message.

If you select the Cascade Update Related Fields check box when you define a relationship between tables, any time that you change the primary key of a record in the primary table, Access automatically updates the primary key to the new value in all related records.

If you select the Cascade Delete Related Records check box when you define a relationship between tables, any time that you delete records in the primary table, Access automatically deletes related records in the related table. For example, if you delete a customer record from the Customers table, all the customer's orders are automatically deleted from the Orders table. If the check box is selected for the relationship between Orders and Order Details and you delete the customer record, as a result, the records from the Orders and OrderDetails tables are automatically deleted.

There are cases where referential integrity rules must be enforced, but the related records must not be deleted. For example, deleting a record from an Employees table does not require deleting the orders that were handled by this employee, so the Enforce Referential Integrity check box is selected in the relationship between the Employees and Orders tables, but not the Cascade Delete Related Records check box. But when you try to delete the employee record, Access displays an error message and cancels the operation. In such cases, you need to open the Orders table, find the records that reference the employee ID, and edit the EmployeeID field to replace the ID with the ID of another employee. This is important to avoid having orders assigned to employees who are no longer with the company.

Edit Employee column of Customers datasheet to eliminate all references to employee

After you replace all references to the ID of the employee whose record you want to delete, you can delete the record from the Employees table.

Top of Page Top of Page

Before you delete any records

Before you delete any records from a database, do the following:

  • Make sure the database is not read-only. To review the status of the database, in Microsoft Windows Explorer, right-click the database file (.mdb), and then select Properties. In the Properties dialog box, review the Read-only check box. If it is selected, clear it.
  • Verify that you have permissions to delete records from the database.
  • Ask all other users of the database to close the objects that you want to work with. This helps avoid lock violations.

 Tip   To get exclusive access to a database, open the database. On the Tools menu, click Options. On the Advanced tab, under Default open mode, click Exclusive. But remember not to open a database this way unless it is really necessary.

  • Back up your database before you start to edit or delete records. Some delete operations can be reversed, but it is safe to make a backup so that you can always go back to the original copy if you need to.

To back up a database, on the File menu, click Back Up Database. In the Save Backup As dialog box, specify a name and location for the backup copy, and then click Save. Access closes the original file, creates a backup, and then reopens the original file. 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 it in Access.

  • Review the implications of deleting records. Identify the relationships involving the table from which you are deleting records, and review the Enforce Referential Integrity and Cascade Delete Related Records settings for each such relationship.
    • If you want the referential integrity rules to be enforced, and the relationship meets the conditions for enforcing referential integrity rules, select the Enforce Referential Integrity check box. Selecting this check box enables you to opt for automatic deletion of related records.
    • If you want the related records to be automatically deleted, select the Cascade Delete Related Records check box.

 Note   If you select this check box for a related table, you should review the settings of all the relationships that this related table is a part of.

  • If you don't want the related records to be deleted, edit the related records so that they do not reference the ID of the record that you are about to delete.
  • If the table from which you are deleting records is a linked table, make sure that the table supports the deletion of records, because not all linked tables allow you to delete records. For example, you can delete or update records in a linked SQL Server table within Access, but you can neither update or delete data in a table that is linked to an Excel worksheet.

Top of Page Top of Page

Step-by-step information

You can delete a record in many different ways, depending on your specific situation. The techniques that are available range from the deletion of a single record by selecting it in a form to the creation of a query to delete, in a single operation, hundreds of records that satisfy a specific condition.

What do you want to do?

Select and delete one or more records when you browse in Datasheet, Form, or Page view

Delete all records from a table (but not the table itself)

Delete a table and its records

Delete records that satisfy a set of conditions by creating a delete query

Delete duplicate records (the original and duplicate records might be in a single table, or in separate tables)

Delete records that don't have a match in another table

Move records to an archive table or database

Top of Page Top of Page

Select and delete one or more records when you browse in Datasheet, Form, or Page view

  1. Open the table that contains the record that you want to delete in Datasheet view. For example, open the Customers table, and delete the record for the customer Wide World Importers.

 Note   You can use a query, form, or data access page to delete some records in the underlying table. But you must make sure that the object supports deletion in Datasheet, Form, or Page view.

ShowReview the query, form, or page to make sure that it supports deletion

The query must not be read-only in Datasheet view. Some queries, such as union queries, are read-only in Datasheet view. You cannot use such queries to delete records.

To review a form, open the form in Design view. Make sure the form's AllowDeletions property is set to Yes. Also, either the form's RecordSelectors property must be set to Yes or the form must provide a custom button for selecting or deleting records.

To review a page, open the page in Design view. Make sure the AllowDeletions property of the group level that contains the record is set to True. Also, the record navigation section of the corresponding group level must include the Delete button or provide a custom button for deleting records.


  1. To delete a single record in Datasheet view or Form view, select the record by clicking the gray square (record selector) near the left margin. Then press DELETE.

To delete adjacent records in Datasheet view or in continuous forms, click the first record that you want to delete, then hold down SHIFT while you click the gray square (record selector) of the last record. This selects a block of records. Then press DELETE.

To delete a record in Page view, click a field that is part of the record, and then click the Delete button in the corresponding record navigation section.

  1. You see one of the following cautions, depending on the table's relationships and how they are defined:
    • If all of the relationships involving the Customers table (Customers-Orders) and the relationships of its related tables (Orders-OrderDetails), and so on until the last table in the chain of relationships, are defined in such a way that all related records can be deleted (the Cascade Delete Related Records check box is selected in each relationship), you see the following message:

Message prompting for confirmation

Click Yes to confirm. If you are not sure that you want to delete related records, click No and see the section Implications of deleting a record.

  • If one of the related tables in the chain of relationships does not allow related records to be deleted, you see the following message:

Message that delete operation failed because related table does not allow cascade deletes

You see this message if the Orders-OrderDetails relationship does not allow cascade deletes. To troubleshoot this issue, review the relationship settings in the Edit Relationships dialog box. Also, see the section Implications of deleting a record.

  • If one of the relationships only enforces referential integrity but does not allow cascading deletes, you see this message:

Message that you cannot delete customers record

If the Customers-Orders relationship does not allow cascade deletes, you cannot delete a customer record as long as there are order records that reference the ID of the customer Wide World Importers. To troubleshoot this issue, see the section Referential integrity rules must be enforced, but the related records must not be deleted.

  • If the table is not related to any other tables, or if its relationships do not enforce referential integrity, you see this message:

Message prompting for confirmation when there are no related records

Click Yes to confirm.

<Back to Table of Contents>

Delete all records from a table (but not the table itself)

To empty a table, such as the Customers table, open the table in Datasheet view. Right-click the gray square to the left of the first column header, also called the Select All button, and click Delete Record.

Delete all records in table in one operation

You see one of the following cautions, depending on the table's relationships and how they are defined:

  • If all of the relationships that involve the Customers table (such as Customers-Orders), and the relationships of its related tables (Orders-OrderDetails), and so on until the last table in the chain of relationships, are defined in such a way that all related records can be deleted (the Cascade Delete Related Records check box is selected in each relationship), you see the following message:

Message prompting for confirmation

Click Yes to confirm. If you are not sure that you want to delete related records, click No, and see the section Implications of deleting a record.

  • If one of the related tables in the chain of relationships does not allow related records to be deleted, you see the following message:

Message that delete operation failed because related table does not allow cascade deletes

You see this message if the relationship between the Orders and OrderDetails tables does not allow cascade deletes. To troubleshoot this issue, review the relationship settings in the Edit Relationships dialog box. Also, see the section Implications of deleting a record.

  • If one of the relationships only enforces referential integrity but does not allow cascading deletes, you see this message:

Message that you cannot delete Customers record

If the relationship between the Customers and Orders tables does not allow cascade deletes, you cannot delete a customer record as long as there are order records that reference the ID of the customer Wide World Importers. To troubleshoot this issue, see the section Referential integrity rules must be enforced, but the related records must not be deleted.

  • If the table is not related to any other tables, or its relationships do not enforce referential integrity, you see this message:

Message prompting for confirmation when there are no related records

Click Yes to confirm.

<Back to Table of Contents>

Delete a table and its records

Deleting a table does not delete related tables or related records from the database. However, if the table is related to other tables at the time that you delete it, the delete operation deletes the relationship between that table and any other tables. For example, if you delete the Customers table, and if the table is related to the Orders table, the relationship between the Customers table and Orders table is deleted along with the Customers table, but the Orders table is left untouched.

  1. To delete the table and all its contents from a database, select the table in the Database window and press DELETE.
  2. When you are prompted to confirm, click Yes.
  3. If the table is related to other tables, you see the following message:

Message prompting for deleting table's relationships

  1. To confirm, click Yes.

The table, its contents, and all of its relationships will be deleted from the database.

<Back to Table of Contents>

Delete records that satisfy a set of conditions

You use a special type of query called a delete query to delete records that are based on one or more conditions. If the conditions remain the same over time, such as "all orders placed more than 90 days ago," you can specify the conditions in the Criteria row of the query fields when you create the delete query. If the conditions vary from time to time, and you want to specify the conditions when you run the query, you can make the delete query prompt you for input.

Conditions might be based on fields in the same table or in another table. For example, you can delete order records that are based on the dates when orders were placed or based on the city where the corresponding customer is located. In the first case, the OrderDate field is in the same table as the order records; in the second case, the City field is in a different (Customers) table.

Important    A delete query respects the Enforce Referential Integrity and the Cascade Delete Related Records settings of the relationships between the tables. This means that a delete query that attempts to delete order records will fail if referential integrity is enforced in the Orders-OrderDetails relationship but the cascade delete option is not enabled. Similarly, if the cascade delete option is enabled for the relationship, the query automatically deletes the order records and the related records from the OrderDetails table. For more information, see the section Implications of deleting a record.

Let's create a delete query to delete records that correspond to orders that were placed 90 days ago. You then can modify this query to accept input from the user when the query is run. Near the end of this section, you can modify this query to delete records based on values in a related table.

Let's make sure that the relationships are set up properly. Right-click the background of the Database window, and then click Relationships. In the Relationships window, identify the tables that are related to the Orders table and the settings of each relationship. The Orders table is related to the OrderDetails table — the relationship enforces referential integrity rules and allows cascade deletes. The Orders table is also related to the Shippers, Employees, and Customers tables, but those tables won't be affected by deleted order records.

Tables related to Orders table

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), under Objects, click Queries Button image, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design View, and then click OK.
  3. In the Show Table dialog box, double-click Orders, and then click Close.
  4. On the toolbar, click the arrow next to Query Type Button image, and then click Delete Query. This changes a select query to a delete query.
  5. Drag the asterisk (*) from the table to the query design grid.

Drag asterisk (*) to Field row

In the first column, From appears in the Delete row, and Orders appears in the Table row. This indicates that rows will be deleted from the Orders table.

  1. Next, you need to specify the conditions in the Criteria row. You cannot specify criteria in the asterisk (*) column. You need to add the criteria columns to the grid, and then specify the criteria. In our case, we want to delete records where the difference between the current date and the order date exceeds 90 days, so let's add the OrderDate column to the grid.

OrderDate column in design grid

In the second column, Where appears in the Delete row.

  1. Type <DateAdd("d",-90, Date()) in the Criteria row of the second column. The DateAdd function adds or subtracts a time period to a given date. In this example, the function returns the date of the day that falls 90 days prior to the current date. The "<" operator compares this date with the date that each order was placed and selects records where order dates are older than this calculated value.

For more information about the DateAdd function, see the MSDN Web site.

  1. On the toolbar, click View Button image to preview the records that will be deleted. If the current date is 10/14/1996, records of orders placed prior to 7/17/1996 will be deleted.

Order records that will be deleted

  1. Save the query as Delete Orders, close the query, and then return to the Database window.
  2. Find the query in the Database window and double-click it. If you have not turned off the confirmation messages, you will be asked to confirm whether you want to proceed with the delete operation. Click Yes twice to confirm that you want to delete records by using the query.

ShowI don't want to see these message boxes each time I run this query

On the Tools menu, click Options. Click the Edit/Find tab. Under Confirm, clear the Action queries check box.


  1. If you followed all the preceding steps, if you have permissions to delete records in the database, and if the database is not locked or read-only, the delete query will succeed in deleting the old records. Open the Orders table in Datasheet view to verify that the records were deleted. Also, open the OrderDetails table to make sure that the related detail records were deleted.

Now, let's modify the Delete Orders query so that it prompts you for a custom date range to use as criteria for deleting records. Let's start by writing the expression, assuming the dates don't change. In this case, the expression looks like ">date value And <date value". Now replace the dates in the expression with messages that are enclosed in square brackets, such as [Enter start date] and [Enter end date]. The expression now looks like this:

>[Enter Start date] And <[Enter End date]

When you run the query, Access tries to evaluate the criteria. If the criteria include the messages, Access displays the messages in separate message boxes, as shown here.

Message boxes prompting for parameters to evaluate query criteria

When you enter a value in the first message box and click OK, Access displays the second message. The values are then used in place of the messages to evaluate the criteria.

Note that you can display multiple messages. The messages might be for a single field or for multiple fields. For example, to display orders that were placed between any two specific dates by a specific customer, you can prompt the user three times — for start and end dates, and for CustomerID.

In this example, let's display two messages — one for start date and one for end date, to allow the user to specify the date range for deleting records.

  1. In the Database window, find the query and click it once to select it. Do not double-click it, because that will run the query.
  2. On the Database window toolbar, click Design to open the query in Design view.
  3. In the grid, delete the contents of the Criteria row of the OrderDate column. Then type >[Enter Start date] And <[Enter End date] in the Criteria row.
  4. On the toolbar, click View Button image.
  5. You see the message for the start date. Type a date, such as 1/1/97, as shown in the following illustration.

Enter Parameter Value dialog box

  1. Click OK. You see another message that asks for an end date. Type 12/1/97, and then click OK.
  2. You see the order records where the order date falls between 1/1/97 and 12/1/97. If you specify these values when you run the query, the records that you see in the view will be deleted.

Records that will be deleted when you run query

  1. Save and close the query, and then return to the Database window.
  2. Find the query in the Database window and double-click it. Click Yes once, and then specify the start and end dates. Click Yes again to confirm that you want to delete records by using the query.

ShowI don't want to see the confirmation messages each time I run this query

On the Tools menu, click Options. Click the Edit/Find tab. Under Confirm, clear the Action queries check box.


  1. If you followed all the preceding steps, if you have permissions to delete records in the database, and if the database is not locked or read-only, the delete query deletes the records whose order date falls within the range that you specified. Open the Orders table in Datasheet view to verify that the records were deleted. Also, open the OrderDetails table to make sure that related detail records were deleted.

Let's look at how to delete records that are based on values in a related table. You will create a delete query that deletes the records that were placed by customers who are located in a specific city. The city information is in the City field in the Customers table, a table that is related to the Orders table.

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), under Objects, click Queries Button image, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design View, and then click OK.
  3. In the Show Table dialog box, double-click the Customers and Orders tables, and then click Close.

The tables have a one-to-many relationship, so you see a line that joins the two tables.

ShowI don't see a line that joins the objects in my query

If your query is based on two unrelated tables, or on a table and a query, you will not see a line that joins the two objects. If a delete query is based on unrelated objects, when you run the query you might get the Could not delete from the specified tables message.

To avoid getting this message, build a relationship between the two objects by dragging the common field from the first table or query to the corresponding field in the second table or query. Then right-click the background of the top half of the query window, and click Properties to display the query property sheet. Finally, set the UniqueRecords property to Yes.


  1. On the toolbar, click the arrow next to Query Type Button image, and then click Delete Query. This changes a select query to a delete query.
  2. Drag the asterisk (*) from the Orders table to the query design grid.

Drag asterisk (*) from Orders table to Field row of first column

In the first column, From appears in the Delete row, and Orders appears in the Table row. This indicates that rows will be deleted from the Orders table.

  1. Next you need to specify the conditions in the Criteria row. You cannot specify criteria in the asterisk (*) column. You need to add the criteria fields to the grid and then specify the criteria. For example, you want to delete records of orders that were placed by customers who live in London. Drag the City field from the Customers table to the second column in the query design grid.

In the second column, Where appears in the Delete row.

  1. Type = "London" in the Criteria row of the City column.

 Tip   To allow users to type a city when they run the query, type =[Enter customer city] in the Criteria row.

  1. On the toolbar, click View Button image to preview the records that will be deleted.

Orders from customers in London

  1. You see the records of all the orders that were placed by customers who are based in London. Save and close the query.
  2. Find the query in the Database window and double-click it. If you have not turned off the confirmation messages, you will be asked to confirm whether you want to proceed with the delete operation. Click Yes twice to confirm that you want to delete records by using the query.

ShowI don't want to see these message boxes each time I run this query

On the Tools menu, click Options. Click the Edit/Find tab. Under Confirm, clear the Action queries check box.


  1. If you followed all the preceding steps, if you have permissions to delete records in the database, and if the database is not locked or read-only, the delete query deletes the orders that originated from London. Open the Orders table in Datasheet view to verify that the records were deleted. Also, open the OrderDetails table to make sure that the related detail records were deleted.

<Back to Table of Contents>

Delete duplicate records. The original and duplicate records might be in a single table or in separate tables.

There are many scenarios that involve finding and deleting duplicate data. These scenarios and the step-by-step procedures for finding, hiding, and deleting duplicate data are described in the article Find, eliminate, or hide duplicate records in Access.

Delete records that don't have a match in another table

Let's say that referential integrity rules for the relationship between the Customers and Orders tables weren't enforced at the time the tables were created. Over time, when customer records were deleted, the corresponding order records weren't deleted. Now you have several order records that either reference an invalid customer ID or are set to Null. A find unmatched query will help you to find all those records where the CustomerID field is set to a value that doesn't exist in the Customers table, including Null values.

Let's create a query by using the Find Unmatched Query Wizard. This wizard creates a special query that compares two tables and returns only the records from the first table that don't have a match in the second table. You can then open this query in Datasheet view and delete all the records that are displayed in the view.

  1. In the Database window, under Objects, click Queries and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Find Unmatched Query Wizard and then click OK.

ShowI get the message "The wizard is not installed or has been disabled"

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.


  1. Select the Orders table, because it contains the records that you are interested in. Then click Next.
  2. Click the Customers table, because this is the table that you want to compare against. Then click Next.
  3. You should see the CustomerID field selected in both lists. This indicates that the tables will be joined, based on customer IDs. Click Next.
  4. Select the fields that you want in your view. In this case, add the OrderID, CustomerID, and OrderDate fields. Click Next.
  5. Accept the name that is suggested for the query, and click Finish to view the records in Datasheet view. You see the records where the CustomerID field is null or invalid, as shown here.

Records with null or invalid values in Customer column

  1. Delete all or some of the displayed records. For step-by-step information, see the section Delete records in datasheet view.

<Back to Table of Contents>

Move records to an archive table or database

You might want to delete records from time to time to keep your database current and the file size manageable. For example, at the start of each quarter, you might want to move the data in your sales database for the previous quarter to an archive database.

Moving records to an archive table or database involves the following steps:

  1. Identify the records that you want to archive. You might want to archive an entire table, or just a few records that meet a set of conditions.
  2. Copy these records to the archive table. You use an append query to do this.
  3. Delete these records from the original table. You use a delete query to do this.
  4. Optionally, you can create a macro to automatically execute steps 1 through 3.

For detailed information about how to create the queries and the macro, see the article Periodically archive records in an Access database.

<Back to Table of Contents>

Messages summary

The following table summarizes the various messages that you might see when you delete one or more records.

Message What it means What you can do
Records not deleted. Data is read-only.    (In the status bar) The database is read-only. Change the file settings so that the database is not read-only.
Records can't be deleted with this form.    (In the status bar) The form does not allow deletion of records. Set the form's AllowDeletions property to Yes.
Could not delete from the specified tables.     Your delete query is based on tables and queries that are not related. The query is unable to bring together the records in two objects.

Relate the two objects in the query Design window. Drag the common field from the first table or query to the corresponding field in the second table or query.

Sometimes, you also might need to set the query's UniqueRecords property to Yes. Right-click the background of the top half of the query window, and click Properties to display the query property sheet. Then set the UniqueRecords property to Yes.

The records cannot be deleted or changed because table table name includes related records.     The relationship between the table and the table mentioned in the error message enforces referential integrity but does not allow cascade deletes. Either enable cascade deletes by selecting the Cascade Delete Related Records check box in the Edit Relationships dialog box, or make sure that the related table does not reference the values that you are about to delete.
Cannot perform cascading operation. Since related records exist in table table name, referential integrity rules would be violated.     One of the related tables in the chain of relationships enforces referential integrity but does not allow cascade deletes.

Review the settings of the affected relationship in the Edit Relationships dialog box. If the Cascade Delete Related Records check box is not selected, select it.

Or edit the contents of the table mentioned in the message so that it does not reference the values that you are about to delete.

Relationships that specify cascading deletes are about to cause n
record(s) in this table and in related tables to be deleted.
    

Are you sure you want to delete these records?    

All tables that are directly and indirectly related to the table allow cascade deletes. The operation will delete records in the current table and in one or more related tables.

Click Yes if you want to delete these records.

Click No to cancel the operation.

You are about to delete n
record(s).
    

If you click Yes, you won't be able to undo this Delete operation. Are you sure you want to delete these records?    

Deleting records from this table does not affect other tables.

Click Yes if you want to delete these records.

Click No to cancel the operation.

You can't delete the table table name until its relationships to other tables have been deleted.    

Do you want Microsoft Access to delete the relationships now?    

The table that you are trying to delete is related to other tables. Click Yes to delete the table, its contents, and all of its relationships.

Top of Page Top of Page

Related tasks

Write a DELETE statement to use with code    See the topic DELETE Statement for more information.

 Tip   The easiest way to get the correct SQL statement to use in code is to build the query in Design view and then switch to SQL View to see the query-generated SQL statement. You can then copy and paste this statement into your code.

Compact an Access database    After you delete a significant number of records, you might want to compact a database to reduce file size and improve performance. For step-by-step information, see Compact and repair an Access file.

Reset an autonumber field    After you delete records, you might notice gaps in the values in an autonumber field. Deleting records or compacting a database does not automatically reset the values in an autonumber field. To learn what you need to do to reset an autonumber field, see the Microsoft Knowledge Base article How to reset an AutoNumber field value in Access.

Top of Page Top of Page

 
 
Applies to:
Access 2003