[AccessD] Missing records

I disallow deletions from forms and restrict access to the database window.  
Forms have a delete button that marks a record deleted.  I believe John uses 
a similar approach as I think I've heard it dicussed here before.  There is 
also an undelete.  If for example, if one of several contractors associated 
to a master estimate record is deleted and a user attempts to add the 
contrator again, that would violate an index restriction (Estimate PK and 
Contractor PK duplication), the user is notified that the contractor record 
on the subform exists as a deleted record, please undelete.  If a parent 
record is deleted, I iterate the subform recordsetclone.eofs and advise of 
related records that will be marked deleted and prompt the user.  The 
undelete 'cascades' in the same fashion, and the user has a warning about 
specific categories of related records.  Each parent and each level of 
subform has its own delete button.  Every form recordsource has a where 
condition 'Deleted = False" unless in undelete mode.

That and daily daily backups plus many full copies at various times of the 
day on many laptops.

So far this approach has proved almost infallible.  Almost, because users 
have occasionally 'reused' a record by changing a record (perhaps 'natural 
key' information) from one to another rather than deleting.  I now store a 
time stamp and UserID history for edits and adds and keep the last month's 
data so we can track who is responsible for the most common data errors in 
order that we can provide a bit of education and assistance (no Colby Mexico 
solution allowed here).

Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com



>From: "Gustav Brock" <Gustav at cactus.dk>
>
>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