[AccessD] Missing records

Hi John

Oh no, why are you beating this horse again?

Cascade delete is a very powerful and useful feature.
However - just like fire - implement it only when you know what you are doing.

Your example with invoices is bad. You never delete an invoice, and if you do (it might be a draft only) you will wish to delete all its invoice lines too. If you don't, you will have orphaned invoice lines, or you will have to bother the user with deleting line by line until the draft is empty and can be deleted.

You may program your own routines to be fired at a click of the supervisor's button, but doing so is somewhat similar to building unbound forms in Access.

As I wrote last time: You know all about this - normalization, relations and so on - so why be so scared?

/gustav

>>> jwcolby at ColbyConsulting.com 03-02-2006 16:33:46 >>>
John,

The first thing to do is examine the relationships for the tables missing
records.  If "cascade delete" is turned on (a NO-NO in my humble opinion)
then deleting a single record (such as a customer) will (after a warning of
course) delete ALL child/grandchild/...greatgreatgreat records.  

Let's say that a client has invoices and invoice line items.  Yep, all gone.
Repair service calls / line items?  Yep, all gone.  Payments?  Yep, all
gone.

Cascade delete of a single (for example) customer record WILL delete all
child records, however far down they go.  Potentially dozens or even
thousands of records, all gone because the user was "just deleting a single
customer record".

I pretty much design my databases to never turn on Cascade delete, and then
build delete queries tied to buttons which only supervisors can see/click.

John W. Colby
www.ColbyConsulting.com