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

Article translations Article translations
Article ID: 240098 - View products that this article applies to.
This article was previously published under Q240098
Moderate: Requires basic macro, coding, and interoperability skills.

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

Expand all | Collapse all

On This Page

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 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:
Could not delete from specified tables.

Properties

Article ID: 240098 - Last Review: July 16, 2004 - Revision: 1.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbprb KB240098

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com