How to avoid records missing in database

[Kayleigh]

Hi there,

A while ago I worked on an OMS system for a company. Every now and then they request maintenance.
The system is based on SQL Server with Microsoft Access front end.

Recently they complained of records going missing. When investigated it seems that these jobs are in the system for some time, with related records being created and edited (as can be viewed on an audit trail) but at some point it all disappears - which would appear to be a deletion but it is difficult to pin-point exactly where this is happening so we can find a way to resolve this for future.

Can anyone advise on how we can find how this is happening and to avoid in future please. As it is having serious business consequences.

Many thanks

 

[Minty]

One way is to remove the ability to delete records in the forms that are used - mark the record as archived instead?
If your users can get to tables directly that can be more troublesome.

You could add Delete triggers in SQL server to the tables concerned and block the deletion.
This would probably raise an error in Access and someone will hopefully mention it at least you would be aware of who and how then?

 

[The_Doc_Man]

You have an audit trail. The problem is knowing when something disappears. Do you have audits of deletion as well as your creation and edit actions? Do you have usable backups that you could use to determine dates between which something goes "missing"?

The "avoid in future" will require a discovery of "when" (followed by "why"). Since you have SQL server, can you set up some sort of trigger on the job records table to see if an actual deletion is occurring?

 

[561414]

Maybe check if they're replicating to another database and the replication is correctly setup.

 

[gemma-the-husky]

One of the issues with deletion by pressing the delete key is that there does not seem to be any event that can be trapped for an "after delete" process.

Where I want to manage deletes carefully I remove the deletes permissions from the form, and always use a "delete" button to get full control. That way I can store an audit trail of the delete.

Could the deletes be happening as part of a cascade delete? eg A record is deleted such as a customer account, and that causes related records such as partial orders to also be deleted? Again, I don't like cascading deletes/updates.

 

[Isaac]

Hi there,

A while ago I worked on an OMS system for a company. Every now and then they request maintenance.
The system is based on SQL Server with Microsoft Access front end.

Recently they complained of records going missing. When investigated it seems that these jobs are in the system for some time, with related records being created and edited (as can be viewed on an audit trail) but at some point it all disappears - which would appear to be a deletion but it is difficult to pin-point exactly where this is happening so we can find a way to resolve this for future.

Can anyone advise on how we can find how this is happening and to avoid in future please. As it is having serious business consequences.

Many thanks

Yes, possibly use bound forms with a BeforeDelete event that cancels, requiring a Deleted flag=1 instead. (etc).
Investigate any possible enforcement of referential integrity