Deleting records in A2K (A2K SR1)

Topic

New Reply

If I delete a reord in dBase-compatible databases/tables, the records isn’t really zapped but only marked for deletion, and I can undelete it later if I need to. In A2K, a record/row deleted is lost permanently unless I wrapped it up in a transaction which will be lost too if I terminate the transaction. It is only good for rollback while the transaction is in process. Do you think it a good idea if I created a deletion marker field ala dBase so that I can use recordsets that aren’t marked for deletion? Is there a better method to keep deleted records for later retrieval if the need arises?

Reply | Quote

Replies

My understanding was in rdbms, once you did the delete, and then the commit, the data is gone. Ok in Access the data is still in the mdb file, until you do a compact, but I don’t know of any ‘easy’ way to get it back. If you want users to have the option to “delete“, but not actually delete the record, then your suggestion of a deletion marker seems fine. Maybe have the users look at the data using a query with a check “deletion maker = False”
You could even do a move operation, and append the “deleted” records to another table

Reply | Quote

In dBase, etc., there was still a chance to retrieve deleted stuff until the database had been packed. In Access, compacting removes temporary objects created by the application and otherwise reclaims resources, as well as permanently deleting objects you have “deleted”. The process is very different from an xBase pack.

Reply | Quote

Here is my . I’m curious myself what others will say.

If you want to keep old records, using a marker is a good idea in itself. It’s fairly easy to build queries that filter out “deleted” records and use these for forms and reports. And it’s easy to restore a deleted record.

I have used the idea of a deletion marker in databases where the number of deleted records is relatively small.
I use 3 fields for this:

• A boolean field Deleted.
• A string field that holds the name of the user who marked the record for deletion.
• A date/time field that holds the date and time the record was marked.
  [/list]If the usage of your database involves adding and deleting lots of records all the time, the database might grow too much and performance might deteriorate if you just mark records.

  Alternatives might be to migrate records to a separate “Deleted records” table – in the same database or in a separate database. You must take special care with parent-child relations.

Reply | Quote

I generally don’t bother with the date and user fields, but the Deleted field is a pretty standard method for enabling “deletions” while allowing the user to have second thoughts. Of course, you have to take extra steps to make sure they can’t really delete a record from a form, and there’s no way to do that in a table or query except by use of Access security.

Reply | Quote

Mostly, storing the user name and date of deletion is not very important. But it can be very handy at times. In one of my databases, “standard” users have the right to delete records (by using a custom command button that issues dire warnings etc.), but can’t see or restore “deleted” records themselves. A user came complaining to me that Access had deleted a record spontaneously. She was absolutely certain that she hadn’t deleted it herself. But the record proved that she had – three days earlier. I would have had a hard time convincing her without having the name, date and time.

Reply | Quote

That’s one of the very reasons we track user name, date and so forth. In particular, we had problems with people editing the address for the wrong person, and by doing archiving, we were able to spot a problem person, and we were also able to restore the correct data. It can be a resource hog, but it sure is useful on occasion.

Reply | Quote

FWIW, we use a “deleted” marker in records if there is any question about ever wanting to restore things, and we set security such that users cannot actually do the delete of a record. It takes a pretty large table to see any significant performance impact, so we don’t normally worry about that. And if we do get a really large table, we normally move it to SQL Server where we have some other tools. In that case, we typically set up an archiving process so we can see a complete history of who has changed a record, where it was changed, and when. In some cases we force a user to give us a reason for the change as well. In that case the archive table can contains multiple copies of a given record, so it can get really large. But with storage being inexpensive, it’s a great tool for users and for managing user training or performance problems.

Reply | Quote

This is something I’ve been wanting to implement in my time tracking application. I get calls from users on occasion that the application mysteriously changed something and I expect it is a training issue. It would be great to be able to go into a table and see exactly what was changed by whom etc. Can you share any more info on the subject? Or should I make this a separate post?

Reply | Quote

If you are using SQL Server as the back-end of your database, triggers are the slickest way to do this kind of thing. They operate independent of the front-end, and are executed (if they exist) each time a record is inserted, changed or deleted. The details of writing triggers are beyond the scope of this forum, but you can pick up any recent SQL Server book and find out more about them.

If, on the other hand, you are running just Access, it’s far more difficult. In Access, you would need to run BeforeUpdate events on the form modifying a record, and be sure you prevented users from getting at data at the table level. Given the level of difficulty, we don’t do that much in Access back-ends. In those cases, we typically use the BeforeUpdate event to set who changed the record and when at the form level. Hope this helps.

Reply | Quote

The back end is in SQL Server. I will look into triggers and see if they will suit my needs. Thanks for the tip, Wendell!

Reply | Quote

Triggers are one of the niftiest features of SQL Server. You can do things with them that are impossible to do in Access itself.

Reply | Quote

I created an application that tracks employee vacation. I use a status field to keep track of whether they are active terminated or deleted. When the employee leaves the firm their status changes to terminated. After two years their status changes to deleted and they no longer appear on the form. This is in case they are rehired and they want there old accrual levels. The back end is on SQL server so storage is not an issue and there are not huge amounts of records.

Reply | Quote

There is a problem with that plan if you use autonumber keys. If you remove records at the end of the table and then compact the database, the autonumber gets reset to the next highest number, which may already belong to one of the records you moved.

Reply | Quote

I am under the impression that autonumber once given, is given, even if a record with that autonumber is removed, leaving gap in the autonumbering. I do use autonumber in my database. If I have a 100 records with the same sequential autonumber, and I removed record number 50 (also autonumber 50), the autonumber 50 will not be issued again. Or does it affect only the highest autonumber, meaning the 100th reord? If I remove record with autonumber 100, compact the database, then the first new record wii have autonumber 100. Is it what you said?

If I use autonumber as my row ID, but keep the records marked with deletion in the same table as the main one, then there will not be any problem?

Reply | Quote

If you remove the last record or records from the table and then compact the database, those number at the end will be reused. You could easily wind up with a record 100 in your “deleted” table and a different record 100 in your active table. If you simply mark the records as deleted using a Yes/No field and leave them in their original table, you won’t have any autonumber problems.

Reply | Quote

In Access, compact applies to the entire mdb, not just to a table or even all tables. Compacting a database may also get rid of oddities in code, and it’s the first thing to try if a database starts misbehaving. If by your question you mean that you have to close the database in order to compact it, that isn’t completely true. You can use the Database Utilities menu to compact the currently open database, although many, if not most, books recommend the other method. I’ve never had any problem with compacting from the open database from the menu though. Does that answer your question or did you mean something else?

Reply | Quote

Yes, I can compact my database using the Access’s Database Utilities, but what if I want to do the same in codes within the database, say as an option in the user’s switchboard or user’s menu, without the benefit of the Database Utilities in the Access’s menu?

Reply | Quote

You can compact the database from code, but not to itself. If you do a Lounge Search in this forum, you’ll find plenty of threads on compacting databases.

Reply | Quote