[AccessD] Missing records

>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.

I actually trap the error that Jet generates when a parent record delete
fails because child records exist and cascade delete is off.

John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jürgen Welz
Sent: Friday, February 03, 2006 12:37 PM
To: accessd at databaseadvisors.com
Subject: Re: [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