Users deleting records?

[BaudKarma]

I've got a database with about a dozen users entering records. Records can be fairly complex, with 20 or 30 minutes spent on entering each one.

On occasion, a mistake is made and a record needs to be deleted. Obviously, I'm leery about giving users the ability to delete records without some sort of safety net. I'm considering trying what dBase used to do... instead of physically removing the record, just set a "Deleted" flag to true, and make sure that all the user forms and reports are set up so that they don't display deleted records.

Any thoughts about using this approach in Access? Is there an easier way to keep users from accidentally deleting things?

I try not to let my ignorance prevent me from offering a strong opinion.

 

[jsteph]

Depending on how many forms, reports use this table, the 'deleted' flag can be a real pain.

How about restricting deletes from all users but the 'master' user, then having a 'Delete' button, and in it's code just write the record to an identical table as the one being deleted from, and then delete the real record? Wrap it in a begin/commit transaction. The delete query can be an OwnerAccess query so only via this query users can delete.

Say the table is tblCustomers, the other would be tblCustomers_Deleted, and would be identical but with some added fields, such as data/time deleted, user, computername, etc.
--Jim

 

[BNPMike]

I'm glad you didn't design my car. You'd have prevented me from taking out on the road lest I smacked into something. Liaise specifically with the Business on this.

Another way to protect yourself is to take frequent back-ups or use MSDE which keeps transaction logs.

Logical deletion is alright except where you then re-enter the same record which of course is likely to happen.

 

[Mike555]

Here's what I did recently to address this issue...

Let's say you are deleting records from a Customer table.

1. Create a table ('tblDeletedCustomers') which will hold several important pieces of information about the deleted record after it is deleted.

2. When the user performs the deletion, insert the fields containing the important information from the deleted record into tblDeletedCustomers, and then delete the record from it's original table.

3. One of the fields in tblDeletedCustomers should contain the username of the individual who performed the deletion.

4. Give the users a way of viewing the records in tblDeletedCustomers.

Now, when a problem arrises due to the fact that something was deleted, users can view the contents of tblDeletedCustomers and see, among other things, the individual who performed the deletion. The individual who performed the deletion essentially becomes the 'go-to' person at this point, and the users don't come screaming to you saying "HELP - I'm missing data."

HTH.