Cascade Delete & VBA (A2000)

Topic

New Reply

Greetings loungers
I wish to toggle with code the Cascade delete option.
The problem involves 3 tables with 1 to M to M relationship.

Deepest sympathies from all New Zealanders to all victims of the atrocities in Manhattan.

Geof Richardson

Reply | Quote

Replies

Could you explain what you want to accomplish? I’ve never needed to “toggle” cascades, so I’m baffled by the question.

Reply | Quote

Hi Charlotte
I want to give users the ability to delete a record in the primary table and the associated records in two related tables of the 1-M-M but I dont want to have cascade deletes as the default referential integrity settings.

I was thinking that I would create a routine that defined a delete querydef and toggle the cascade property of the relationship.

Am I barking up the wrong tree ?
Cheers & thanks
Geof

Reply | Quote

Like Charlotte, I’m a little baffled by the situation. The usual reason you disable cascade deletes is to protect data in a table from deletion, and as a way to help enforce referential integrity. If you are going to toggle cascade deletes, I’m not sure why you would bother to turn it off originally?

I’d leave it off and do a series of delete queries, working “upwards” from the lowest “child” table.

Reply | Quote

Thanks Mark & Charlotte
I will work on series of delete from the lowest upwards.
I
The primary table has Yes/No field.

I am querying for “Yes” and copying the results (from primary table only) to another table. I then wanted to cascade delete.

Cheers & thanks for the help.

Geof

Reply | Quote

I think you’re going to build yourself some problems. You’re moving a record in a primary table (not the best idea in the first place) and deleting related records. That means those related records are gone for good. Is that really what you want to do?

Reply | Quote

Hi Charlotte
Thanks for the thoughts.
In this case it is fine to move these records and delete the associated records.

This situation involves product pre purchase inspections. If a decision is made to purchase then a record is populated in the stock table. The inspection results are no longer needed.

I perceived a need to keep the tblStock and TblPP-Stock separate.
Probably breaking a few rules along the way. However all seems to be working at present.
Cheers
Geof

Reply | Quote