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

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.

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.

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.

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.

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?