[AccessD] Missing records

John Colby jwcolby at ColbyConsulting.com
Fri Feb 3 12:30:37 CST 2006


>Just yesterday I had to restore about 14,000 expense records because she
had deleted an employee rather than "terminating" (which means enter the
date the employee was terminated) that employee.

Now imagine that this occurred a year ago?  How do you find the backup?
People aren't always aware that what they did was a mistake.  It can be
months before some vice president generates a report somewhere and says
"hmmm... Something doesn't look right here".  You were lucky and discovered
the error immediately.

Long ago I designed an active/trash system for my databases.  A user clicks
"delete", I intercept the error from Jet saying that there are child records
and set the trash flag and clear the active flag.  Requery the form, and
since only active records are displayed, the "deleted" record disappears.  I
can't tell you how many times I have "undeleted" a record. 

Very satisfying actually.

If the record actually is trash, a periodic process reports all the trash
records and a supervisor can decide whether to actually delete the records.

John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Reuben Cummings
Sent: Friday, February 03, 2006 10:48 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Missing records

I think John nailed it.  Check all relations to this table.

Just yesterday I had to restore about 14,000 expense records because she had
deleted an employee rather than "terminating" (which means enter the date
the employee was terminated) that employee.

Cascade Delete is very powerful, which makes it very scary.

I now have two things to change in my app - a relation property and to not
allow deletes of employees.

Reuben Cummings
GFC, LLC
812.523.1017


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
> Sent: Friday, February 03, 2006 10:34 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Missing records
>
>
> 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
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Clark
> Sent: Friday, February 03, 2006 9:51 AM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Missing records
>
> We got a call from a department that has a small Access 2K db.
> "Something" has happened, and they are missing about a quarter of 
> their records. This was being investigated by a technician and I was 
> just asked the following question:
>
> If a PC is "hard-booted" can an Access DB lose records w/out showing 
> signs of corruption (i.e. the db still runs)?"
>
> I really don't know the answer to this question. I have had nearly no 
> experience w/db corruption, since starting with Access 5 years ago. I 
> used to use FoxPro...there are still some old FoxPro 2.6 (DOS) dbs 
> hanging around actually...and corruption was a huge problem w/them.
>
> Anyone got any tips on this?
>
> Thanks!
>
> John W Clark
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>




--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list