Cascade Record and Image Deletes

In this example we will look at deleting a category in a typical product catalog with Product_Categories, Products, and Versions. Having more than one table imposes difficulties that can only be overcome by a little hand coding and modification of stock UD behaviors. This tutorial details how to set up the relationships in an Access database for cascade deletes of related records [*] and how to implement server side code to delete images associated with related records. [†]

I will set up some conventions to make it easy to follow the tutorial. This tutorial assumes you have created a recordset navigation page to select the record you want to delete and a method to select the desired record. It assumes you have added a form to the delete page with a method to submit the delete. It assumes you have created an Access database with the described table structure or your customized version of it.

Color conventions will be used to make following the tutorial as simple as possible. Stock UltraDev code blocks that are not modified in this tutorial will be black. Tutorial code will be blue. Comments pertinent to the tutorial will be grey. We will begin with the Access database.

Relationships

The catalog database will need the following structure: Tables for Product_Categories, Products, and Versions, each of the tables having an image field so that as many as three images may be associated with any given Product_Category > Product > Version set. The tables are related by primary key values in the following manner: the Category_ID field relates the Products table to the Product_Category records. The Product_ID field relates the versions to products.

Open the database in Access and select the relationships window using the icon shown in the heading of this section. It is found in the main toolbar. The relationships wizard will appear as shown above. Highlight and add the tables for which you want to define relationships. Add the Product_Categories, Products, and Versions tables to the relationships layout window. Next click on the Category_ID field of the Product_Categories table and drag the icon that appears at your mouse pointer to the Category_ID field of the Products table. The define relationships window will open.

As you can see the correct tables and fields are selected in their respective boxes. You will, however, need to check “Enforce Referential Integrity” and the “Cascade Delete Related Records” checkboxes. Now repeat the process for the Products and Versions tables. When you finish the relationships should something like the image below.

Save your changes and close Access.

[*] The Access Upsizing Wizard will convert the relationships to triggers when upsizing the Access database to SQL Server removing any need to write the SQL statements.

[†] This same method of coding will work with structures that have fewer or more tables by merely adding or taking away recordsets and loops.