Delete query - delete from specified tables error


K

klkropf

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
can't figure out what I'm 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!
 
Ad

Advertisements

J

Jerry Whittle

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's 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.
 
K

klkropf

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

Jerry Whittle said:
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's 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.


klkropf said:
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
can't figure out what I'm 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!
 
J

Jerry Whittle

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.


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

Jerry Whittle said:
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's 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.


klkropf said:
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
can't figure out what I'm 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!
 
Ad

Advertisements

K

klkropf

Perfect! Thank you so much.

Jerry Whittle said:
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.


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

Jerry Whittle said:
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's 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.


:

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
can't figure out what I'm 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!
 

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

Top