Delete query error "Could not delete from specified tables"




I am using Access 2002. I have tried to find the answer to my question by
searching the discussion groups, but am having problems applying those

I have two tables, "defects" and "Defective", which have a one-to-one
relationship of serial numbers. The field "Date" is in table Defective only.

I want to delete all records that are before the first of the year from
those tables. I created a delete query from a select query where the date
criteria is < #1/1/2006#.

My SQL statement from this query then reads:
DELETE DISTINCTROW Defective.*, defects.*, Defective.Date
FROM Defective INNER JOIN defects ON Defective.Serial_Number =
WHERE (((Defective.Date)>#1/1/2006#));

I am getting the error message, "Could not delete from specified tables".

Why is my query not working, and what can I do to correct this problem? I
have already changed the Unique Records property to yes.

Thank you


Are these two tables joined together in the Relationship window between their
primary keys? Is Referiential Integrity enabled? If not, you do not have a
true 1-1 relationship. If you can get either a 1-1 or 1-M relationship to
work between the Defective table and Defects (with Defects on the M side if
necessary), turn on Referiential Integrity then Cascade Delete.

With both RI and cascade delete enabled, the following simple query will
delete records from both tables:

DELETE Defective.Serial_Number, Defective.Date
FROM Defective
WHERE (((Defective.Date)<#1/1/2006#));

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question