Testing Cascading Deletion and Updating

Access 2.0 introduced two new features that were requested by Access 1.x users: cascading deletion and cascading updating of records having a many-to-one relationship. When you delete a record in a primary or base table on which records in a related table depend, cascading deletion automatically deletes the dependent records. Similarly, if you modify the value of a table's primary-key field and a related table has records related by the primary-key field's value, cascading updating changes the value of the related foreign-key field for the related records to the new primary-key field value.

Cascading deletions and cascading updates are special types of action queries that the Jet engine executes for you. The following three sections show you how to use Access's cascading deletion and cascading updating features with a set of test tables copied from the Orders and Order Details tables of Northwind.mdb.

Creating the Test Tables and Establishing Relationships

When experimenting with database features, you should work with test tables rather than "live" data. As mentioned in the note at the beginning of this chapter, using copied test tables is particularly advisable when the tables are participants in action queries. The remaining sections of this chapter use the two test tables, tblOrders2 and tblOrderDetails, that you create in the following steps:

1.	Click the Database window's Tables tab and then select the Orders table from the list.
2.	Press Ctrl+C to copy the table to the Clipboard.
3.	Press Ctrl+V to display the Paste Table As dialog.
4.	In the Table Name text box, type tblOrders2, then click OK or press Enter to create the test tblOrders2 table.
5.	Repeat steps 1-4 for the Order Details table, naming the copy tblOrderDetails.
6.	Open the tblOrders2 table in Table Design view and change the field data type of the OrderID field from AutoNumber to Number, and make sure that the Field Size property is set to Long Integer. (This change is necessary to test cascading updates in the next section.)

Cascading deletions and updates require that you establish a default relationship between the primary and related tables, and enforce referential integrity. To add both cascading deletions and updates to the tblOrderDetails table, follow these steps:

1.	Click the toolbar's Relationships button to display the Relationships window.
2.	Click the Clear Layout button to clear the display of the Relationships window. A message box appears warning you that the Relationships window will be cleared. Click Yes to continue.
3.	Click the toolbar's Show Table button to display the Add Table dialog. Alternatively, right-click the upper pane of the Query window and choose Show Table.
4.	Double-click the tblOrders2 and tblOrderDetails items in the list, and then click the Close button to close the Show Table dialog.
5.	Click the OrderID field of tblOrders2, then drag the field symbol to the tblOrderDetails table's OrderID field to establish a one-to-many join on the OrderID field. The Relationships window appears.
6.	Select the Enforce Referential Integrity check box to enable the two cascade check boxes.
7.	Select the Cascade Update Related Fields and Cascade Delete Related Records check boxes, as shown in Figure 11.24. Figure 11.24. Setting the cascading deletions and updates options.
8.	Click the Relationships dialog's Create button to make your changes to the join effective, and then click the Close Window button to close the Relationships window. Click Yes when Access asks if you want to save your changes to the window's layout.

Testing Cascading Deletion

To try cascading deletion with the test tables, follow these steps:

1.	Open the tblOrders2 and tblOrderDetails tables in Datasheet view.
2.	Click the surface of the tblOrders2 datasheet to make it the active window, and then click a record-selection button to pick an order in tblOrders2 to delete.
3.	Press the Delete key to delete tentatively the selected records and the related order's line-item records in tblOrderDetails.
4.	The message shown in Figure 11.25 appears requesting that you confirm the deletion. Choose Yes to delete the records.

To verify that you have deleted the related records, you can scroll to the related record or records for the order that you deleted in the tblOrderDetails table. If you opened tblOrderDetails in step 1, the data cell values for the deleted related records are replaced with #Deleted.

Testing Cascading Updates

Cascading updates to the foreign-key field of records that depend on a primary-key value that you want to change in a primary table is a valuable feature of Access. Performing updates of primary-key values while enforcing referential integrity is not a simple process; Chapter 4, "Working with Access Databases and Tables," briefly discusses the problems associated with performing such updates manually. To see how Access takes the complexity out of cascading updates, follow these steps:

No confirmation message appears when you execute a cascading update, because the effect of a cascading update is reversible. If you make an erroneous entry that causes an undesired cascading update, you can simply change the entry to its original value by choosing Edit, Undo Saved Record. Alternatively, you can simply reenter the original or the correct value manually.