Originally posted by okaned
I am trying to delete from a table linked to another table using criteria from the other table. When I try and do this I get the error
"Operation must use and updatable query"
I have followed the help in Access 97
"Delete records by using a query that includes both tables in a one-to-many relationship"
but it keeps on giving me the above error.
Any help on this would be much appreciated.
Hullo. Yup, this is a nasty one. The only way I found around it so far is to use a sub-select. So, you have the table that has the records you want to delete. Drag down the * entry from that table, and a field that you would normally join to the other (criterion) table. In the NON-* field, you'd type your sub-select statement in the Criteria area. Consider this SQL:
DELETE DelMeAct.Article, DelMeAct.*
WHERE (((DelMeAct.Article) Not In (select artprocmbd.article from artprocmbd where (artprocmbd.program) like "Christmas*" or (artprocmbd.program) like "Holiday*"or (artprocmbd.program) like "PVC*")));
The WHERE statement holds the sub-select. It helps if you build the sub-select in QBE, and then massage the SQL to fit as a sub-select.