Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Cascade Delete & VBA (A2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cascade Delete & VBA (A2000)

    Could you explain what you want to accomplish? I've never needed to "toggle" cascades, so I'm baffled by the question.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: Cascade Delete & VBA (A2000)

    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

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Cascade Delete & VBA (A2000)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: Cascade Delete & VBA (A2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cascade Delete & VBA (A2000)

    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?
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: Cascade Delete & VBA (A2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •