Delete query error "Could not delete from specified tables"

[Guest]

Hello,

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
answers.

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 =
defects.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

 

[Guest]

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#));