[AccessD] Coding for backing up deletes

Agreed.  I have an active and a trash flag (Booleans).  Active means just
that, active in the database, should be showing at all times.  Inactive
means not ready for archive but still an active record.  Trash means someone
tried to do a delete.

The state table looks like:

Active=True, Trash=False	- Record is active
Active=False, Trash = false	- Record is inactive
Active=False, Trash=True	- Someone tried to delete
Active=True, Trash=True		- Invalid state.

I have relationships set between virtually every parent / child with cascade
delete turned off.  This causes any attempt to delete where a child record
exists to generate a jet error.  My error handler simply asks "are you sure
you want to delete", and if the answer is yes, clears the active flag and
sets the trash flag, requerying the form.  All forms display only Active
records.  Since the active flag was just cleared, the record "disappears" or
appears to be deleted.

You would not believe the relief on the user's face when you undelete a
record they know they shouldn't have deleted.  Worth more good will than
anything else you can do for them.

I am able to prevent deletes on records with no children but in many cases I
don't bother.  Many times a single record with no children should be allowed
to be deleted anyway.  Mistaken data entry, trash records etc.

Since I use a framework, and this stuff is built into the form class, it is
just there, available for any form that uses the form class.

John W. Colby
www.ColbyConsulting.com 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren DICK
Sent: Tuesday, August 03, 2004 8:43 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Coding for backing up deletes


Hi Paul
I rarely let my users 'delete' records
In virtually all my tables I have a field called IsDeleted - Data Type =
Number All my 'delete' buttons actually set the value for IsDeleted in that
record to -1 Then the form refreshes to NOT show records where IsDeleted =-1

That way we can give the appearance (and make yourself look good in the
process) of having the ability to undo or rollback when users 'stuff
something up' after they have left the record or closed the form

I actually then move (at whatever period suits) all records where IsDeleted
= -1 to another table or dB

I rationalise this to all my users (the ones involved at admin level in the
design specs at least) and I haven't 
had anyone disagree (yet). In fact when you explain the benefits and low
overheads al my customers (so far) 
agree it's a great idea.

You can then build them an interface (I do) to recover 'deleted' records or
data. I don't let 'em edit data through those interfaces - just recover
'deleted' records or REALLY delete - depending on Logon Permissions.

It's all pretty transparent to the users and the Admin people really know
what's going on and love the ability of a 2nd chance recovery or proper
deletion

Hope this helps

Darren