How to Force a Cascading Delete


Summary:

The capabilities of referential integrity in Access do not extend to
performing cascading deletes (a way to delete all the records of a
child table when the corresponding record in the master table is
deleted.) This article discusses how to write a macro to perform a
cascading delete upon deleting a record from a related master table.

More Information:

The capabilities of referential integrity in Microsoft Access do not
extend to performing cascading deletes. A cascading delete is a way to
delete all the records of a child table when the corresponding record
in the master table is deleted.

For example, suppose you are viewing the NWIND Categories table, and
you want to delete the category ID "BEVR." If Categories is related to
the Products table that contains many child Category ID records with a
"BEVR" value, and if referential integrity is enabled for this
relationship, Access would not permit you to delete the record in
Categories until you delete all of the child "BEVR" records in
Products. With the ability to perform a cascading delete, all of the
Products "BEVR" records would be deleted automatically before the
actual deletion of the Categories "BEVR" took place.

To force a cascading delete using the Categories/Products example,
first create a macro that attaches to the OnDelete event of a the
Categories form as follows:

1. In the Database window, choose the Macro button, then choose New.

2. If the Condition column does not appear in the macro sheet, choose
   the Conditions command from View menu.

3. Add the following actions and their corresponding properties:

  Condition                                                Action
  ---------------------------------------------------------------

  MsgBox("Delete record & all child records?",33)<>1       CancelEvent
  ...                                                      StopMacro
                                                           SetWarnings
                                                           RunSQL


  SetWarnings Action
  ------------------

  Warnings On:   No

  RunSQL Action
  -------------

  SQL Statement:     DELETE * FROM [Products] WHERE [Category ID] =
                     Forms!Categories![Category ID];

                     Note the semicolon (;) at the end of the SQL
                     statement.

4. Close and save the macro, naming it "Cascade."

To use this macro with an existing form, do the following:

1. Open the form in design mode.

2. From the Edit menu, choose Select.

3. In the property sheet, specify "Cascade" for the OnDelete event.

4. Close and save the form.

Now when you use the form, deleting any records will delete any
matching child records in a related table.

To delete a record on the Categories form (because there are no Record
Selectors on this form), choose the Select command from the Edit menu,
then choose the Delete command from the Edit menu.