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.

 

[Pat Hartman]

I would do all of the above. Talk to your DBA about whether he can search the logs to find the deleted records. This will depend on how long the logs are kept. He should also have backups. You can pick a backup. Say 1 month ago. Is the record there? Move forward or backwards. This is of course very time consuming and the DBA might not be willing.

Setting a trigger is an immediate bandaid to stop it from happening however it is happening. So, I would do that no matter what.

You then should modify the forms that maintain the table to prevent them from allowing deletes or only allowing them under certain conditions. VBA logic will be much more flexible than what you can create with a trigger.

 

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

 

[Pat Hartman]

Cascade delete needs to be handled correctly and with thought. It is far better to allow declarative RI to manage deletes than for the programmer to code for it himself.

If it is ever possible to delete an order, then cascade delete should be specified on the OrderDetails. OrderDetails are useless without an Order. Typically, you would allow orders to be cancelled if they have not yet shipped but only deleted if no items had been added to the order.

Moving up to Customers. Customers should only be allowed to be deleted if they NEVER had any orders at all. Therefore, you would not specify cascade delete on that relationship. That would prevent a user from accidentally deleting a customer who did have orders.

I used my logging tool to confirm/deny your opinion on whether seledting a row and clicking delete fires the correct

form level events. It does. The Events are inverse order and they are numbered so the first event that fires is the Form's MouseDown event as I click the record selector.
The relationship specifies RI but does not specify CascadeDelete. The first picture shows deleting a state that has city records and it raises an error. You can see all the events in the background. The second picture is the same form but I added a dummy state with no city and used the same method to delete it.

This tool can be downloaded from:

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

Use the forms I built or build your own. Just make sure to add the logging code to the necessary events and to open the log form first so that is is open when your form opens in order to capture all the events.

 

[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