Delete query "could not delete from specified tables"

[angloman]

Hi all,

With the help of some of the people here I was able to create a query that selects records from one table that have several fields which don't match with another table.

The end-game with this query is to have it delete the records in table 1 that don't match records in table 2. The delete query tells me that it could not delete from specified tables.

I've attached the select query, the delete query and the error, and the relationship table for the db.

Thanks for your help!

 

[CJ_London]

the table you are trying to delete is in a one to many relationship. Relationship rules means you cannot delete a 'parent' without deleting the 'children'.

Incidentally, you only need one 'is null' criteria, if one is, they all are

 

[angloman]

Oh, you're right about the is null criteria.

I have the child records set to cascade delete. Is that not sufficient? How can I get around this?

 

[CJ_London]

must admit if you have cascade delete, I would have thought that would work - I presume the child table doesn't have children itself?

Your relationship view is not complete (table propro for example) so I would check there aren't any other relationships which exist but aren't displayed.

An alternative would be to add a yes/no field called something like 'ToBeDeleted" to your T_Productions table and change your delete query for an update query to set the field to true

then run a delete query where this field is true

 

[angloman]

The child table doesn't have any children.

ProPro is a linked table which is the source of the data. I don't want to delete anything from ProPro. Is that what I was about to do?

I could definitely do that alternative approach if we can't figure this out.

 

[CJ_London]

I don't want to delete anything from ProPro. Is that what I was about to do?

No - so to clarify since you didn't answer the question, propro does not have any relationships set with T_Production? and there are no other tables that T_Production has relationships with. - The fact the relationship view doesn't show a relationship does not mean a relationship doesn't exist with a table not on the view.

 

[angloman]

As far as I know, ProPro doesn't have any relationships set with ProPro. All of the relationships for the db are in the relationships.jpg from my original post.

When I click "All relationships" under the Relationships window, all I have are the 3 tables T_ProNum, T_Productions and T_Tracking.

 

[CJ_London]

OK, so we can rule that out. Regret I am out of ideas as to why the delete is throwing the error.

 

[angloman]

It's okay! The update query + delete query trick you mentioned works perfectly. Thank you very much for your help!