ACC2000: "Could Not Delete from Specified Tables" Error Message in Delete Query

When you run a delete query, you may receive the following error message:

The delete query contains multiple tables, and the UniqueRecords property is set to No.

Set the UniqueRecords property of the query to Yes. To set the UniqueRecords property of the query to Yes, follow these steps:

1. Open the delete query in Design view.
2. On the View menu, click Properties.
3. Set the UniqueRecords property to Yes.
4. Save the query, and then close it.

When you set the UniqueRecords property to Yes, Microsoft Access includes the DISTINCTROW predicate in the SQL statement of the query. The DISTINCTROW predicate retrieves unique records in a multi-table query where fields have only been selected from the one-sided table in the query.

For example, if you add both the Customers and Orders tables to a query, but only select fields from the Customers table, the query returns multiple rows for each customer who placed multiple orders. When you set the UniqueRecords property to Yes, the query returns only one occurrence for each customer as long as that customer placed at least one order.

Steps to Reproduce Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Open the sample database Northwind.mdb.
2. On the Tools menu, click Relationships.
3. Double-click the line representing the relationship between the Customers and the Orders tables.
4. Verify that referential integrity is being enforced.
5. Click to select the Cascade Delete Related Records check box to enable cascade deletions.
6. Click OK to close the Edit Relationships dialog box.
7. Close the Relationships window.
8. Click Queries under Objects, and then click New.
9. Click Design View, and then click OK.
10. Add the Customers and the Orders tables to the query, and then click Close.
11. On the Query menu, click Delete Query.
12. Drag the asterisk (*) from the Customers table to the first column of the query design grid.
13. On the Query menu, click Run.

Note that you receive the following error message: