I have an intense dislike for cascade deletes in Microsoft Access.  And I don't like cascade updates.

(Last updated 2010-09-27)

Cascade Updates

I don't like cascade updates. If you are using an autonumber primary key in your tables then cascade updates are a non issue because 1) you can't change an autonumber key and 2) the user should never see the autonumber key. If you are using a natural key, for example in Northwinds this would be using CategoryName as a primary key in the Categories table, then I can see this being somewhat useful. I don't care for natural keys though. Access needs some added functionality to support these better such as in the subform creation wizards.

Doing a lot of cascade updates can be more susceptible to performance and corruption problems as cascade updates could need to lock a lot of pages. A table high up on the relationships "food chain", for example, a customer table, could require many thousands of invoices to have the customer foreign key to be changed if cascade updates were allowed.

I also feel that, unlike Tom Ellison who eloquently disagrees with me, primary autonumber keys should be used in all tables. Which are never visible to the user and thus no cascading updates are ever required.

Cascade Deletes

I have an intense dislike of cascading deletes. <smile> Especially when a user sees the Access message they don't pay any attention to the extra wording.  And it can be way, way too easy to start deleting records from too many tables. For example you go to delete a customer and, whoops, cascade delete just removed all the customers invoice headers and invoice deletes. Clearly that's an extreme example but is quite possible if someone doesn't clearly understand what could go wrong.

Without cascading deletes if they went to delete a parent record without deleting the child record they'd get a "Record cannot be deleted or changed because table 'Products' includes related records." With cascading deletes you get the following message.  "Relationships that specify cascading deletes are about to cause 1 record(s) in this table and in related tables to be deleted."    And what user is going to understand the implications of this message. "One record to delete? No big deal. How can it hurt?"  This is a lousy message

Currently in Northwind if you delete a record from the Category table it won't let you due to relational integrity with the Order Details table. I added Cascade Deletes to the relationship between Products and Order Details. And now deleting one record from the Category table deleted 302 records from the Order Details table. And lots of records in the Product table. (I didn't count how many.)

And when do you find out about the deleted Order history? Likely long after the three or five daily backups had been cycled through. Yes, I am trying to paint a grim picture.  It's my job to be a paranoid pessimistic.  Or is it pessimistic paranoid?

In a few limited cases cascade deletes can be useful.  Such as in a temporary table required for doing some data entry which then gets appended to the main tables.   But these situations are far and few between I don't even bother.

Thus I just delete the records in the child table(s) myself using a few lines of SQL code embedded within VBA. For example

where ithID is the tables primary key and is found on the form calling this code.  

To easily get the SQL Code required create a query in Access.   Add all fields and the primary key to the fields grid.   In primary key field place a random value, say 1, in the criteria field.  Make sure your query selects only one record.  Now make it a delete query and then click on the small down arrow to the right of the View button and choose the SQL button.  Then copy and paste the SQL code into VBA and update as you prefer.

Note my naming conventions for the field names.  A little unusual but I've found they work very well within Access.

For ADO use CurrentProject.Connection.Execute strSQL, lngRecordsAffected, adCmdText

[ Access Tips | Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available