DataBase - Delete query - delete from specified tables error

Asked By klkropf

03-Feb-10 01:27 PM

I am running a delete query and am getting the error "could not delete from
specified table".  I have looked at the other entries on this, but still
cannot figure out what I am doing wrong.  Below is the SQL.


DELETE tblDemotionFY10.*, tblQuarters.Quarter
FROM tblQuarters INNER JOIN tblDemotionFY10 ON tblQuarters.MonthNumber =
tblDemotionFY10.MonthNumber
WHERE (((tblQuarters.Quarter)=[Enter Quarter]));

Thanks!

03-Feb-10 01:56 PM

You are trying to delete from two tables at once. That's very hard to do with
Access. If you have the tables joined in the Relationship window with
Referential Integrity enabled, you could enable Cascade Delete. Then if you
deleted a record from the parent table, matching records in the child table
would also be deleted.

Otherwise it is a two step process where you delete records from the child
table first, then delete records from the parent table.

This assumes that you really want to delete records from both tables. That's
how the SQL reads. If you only want to delete records from one table, tell us
which table and we could help.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

03-Feb-10 05:09 PM

I only want to delete records from one table, tblDemotionFY10.

03-Feb-10 05:44 PM

Try this on a backup copy of the database / tables!:

DELETE tblDemotionFY10.*
FROM tblDemotionFY10
WHERE tblDemotionFY10.MonthNumber
In (SELECT tblQuarters.MonthNumber
FROM tblQuarters
WHERE tblQuarters.Quarter=[Enter Quarter]);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

03-Feb-10 05:56 PM

Perfect!  Thank you so much.