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

ID: Q240098

The information in this article applies to:
  • Microsoft Access 2000
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).


SYMPTOMS

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

Could not delete from specified tables.


CAUSE

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


RESOLUTION

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.



MORE INFORMATION

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 select 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: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform 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:
Could not delete from specified tables.

Additional query words: prb can't

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb


Last Reviewed: January 3, 2000
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.