Missing Records

[Design by Sue]

My database keeps track of employees and their training and has been used for about 2 years now. Client complained last week that some employees were missing from the list. Sure enough the first five in the list were gone - including the linked files of their training. This is really weird! The program is written using related tables and custom messages to NOT allow deletion of any record that has linked records. The database is split into a front and backend. Has anyone else had such an experience? The strangest part is that the related records are deleted. I realize someone could have gone into the backend and removed files but it would have had to be someone with a knowledge of Access to realize they needed to delete the related records first then the employee records and in all honesty I do not think anyone working at the company has any experience at all with Access. Could there be a bug somewhere that removes records? I know this is a strange one but figured asking the great internet god might reveal some answers.

Thanks
Sue

 

[spikepl]

Bug? I doubt it. More likely some inadvertent operation.

If Cascade Delete is set to Yes in the relation then deleting a parent wipes out all its children too in one go, which is why this option should only be used with quite some forethought.

 

[Old Man Devin]

If Cascade Delete is set to Yes in the relation then deleting a parent wipes out all its children too in one go, which is why this option should only be used with quite some forethought.

Sounds exactly like a case of this to me. Or perhaps someone really is just trying to clever by deleting things from the backend! Hopefully you have some form of backup from which you can get the records back.

 

[jdraw]

I agree with the others -- sounds like Cascade Deletes.

If you plan any redesign, you may consider shadow delete/logical/delete where you have a boolean field in your record

eg IsDeletedYN --set to N by default, but to Y when record is "deleted"

No physical delete takes place, only a logical delete --so no info is lost. You have to change queries to respect the boolean field.

 

[Design by Sue]

Seems like Cascade Delete would be a problem EXCEPT I do not have the checked in ANY of the relationships! I do have Cascade Update but NOT Cascade Delete.

(Had a close enough back up to clean it up - but still this is so strange)

Sue

 

[jdraw]

Can you tell from Backup and original, when records were lost?
Does that identify any "special database activity"?

No special/admin routines that could have been run?

Good to have Backups.