Delete query error "Could not delete from specified tables"

  • Thread starter Thread starter Guest
  • Start date Start date
G

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