Delete Record but archive (1 Viewer)

I want to enable people to delete records from a form that I have created. On the form I created a button, using MS Access dummy tools to just delete the record. My problem is that I would like to save a copy of the record to an alternate table with a timestamp and the users id, which I have captured on the form. Is there a simple way to do this using access 2013 tools, or will I need to find a VBA solution? I feel like there is something simple its just not occuring to me. I could append the record to an archive table as part of the delete record button or something. Its Friday my brain has turned inward.

Use a field to flag record as 'archived' and apply filter on form to exclude. Perhaps a date/time field for the date of 'archive'. If the field is Null then the record is active.

June's answer implies keeping the records in the same table with a Boolean field e.g Archived set to true for those records. I agree.

Do NOT transfer the records or copy them to another table. It is unnecessary and will cause lots of additional work for no reason at all. I know this from experience having made that decision many years ago and having regretted it ever since

ridders said:

Do NOT transfer the records or copy them to another table. It is unnecessary and will cause lots of additional work for no reason at all. I know this from experience having made that decision many years ago and having regretted it ever since

Click to expand...

A to the Men on that one. Only I didn’t make the mistake, it was made for me, but the clean up was a headache I have not fully recovered from yet!

In fact,... DON'T have a field to flag as archived. Instead, have a field to indicate "Archived By" and if THAT is blank, it hasn't been archived. You can have a date field in there for last update that would also be empty until archiving.

Then instead of filtering by "[ARCHIVED]=TRUE" you filter by "[ARCHIVEDBY]<>0"

I agree that in most cases, "archive in place" is a better solution but depending on your application, that may involve very large changes since you will need to find and modify all queries that use the table and decide when or if they should ever show archived records.

For "archive in place", I just use an archive flag because I use the existing ChangedBy and ChangedDT to store who did it and when.

For "archive and delete", you will need to run an append query that copies the record you want to archive from the main table to the archive table. Then you would delete the record from the primary table.

Most of the archiving I do is then based on when a record was marked as "deleted". To keep the tables as small as possible, I generally run a batch archive every year that removes everything that was archived more than 3 years ago or whatever timeframe makes sense for the application.

Yes, depends on application structure.

What happens with related records in dependent tables? How are relationships maintained? Is autonumber basis for PK/FK?.

June7 said:

Yes, depends on application structure.

What happens with related records in dependent tables? How are relationships maintained? Is autonumber basis for PK/FK?.

Click to expand...

June's answer just spelled out why I bitterly regretted hiving off archived records many years ago

In a large schools database, I decided to store records for student leavers which until then had been deleted when they left. For many reasons that wasn't a good idea but nor was the solution I chose of creating leavers tables (approx 50 of them, duplicating all main student tables). I then had to create routines to transfer data on leaving plus further routines to restore the data if students were later readmitted.
From then on, every change to the main tables had to be duplicated in the leavers tables & the transfer procedures had to be updated as well. Similarly for queries & reports. It became a total PITA.

Worse still around the same time, we also decided to archive attendance, grade & reporting records each year into separate archive tables (approx 30 of those). Same issues. Double PITA.

Thankfully the PK fields weren't autonumber fields or it would have been an even bigger nightmare

Since then, I've NEVER followed that approach & STRONGLY advise anyone else against making the same mistake

For the one case where I had to actually remove data, I asked my management what they thought would be reasonable. The government approach was that if I could keep a record of the information in any other format, just so we could call it up if someone asked about it later, I could do the deletion.

I wrote code to visit the items marked for delete. For each one, I created a file based on the name of the object being deleted. Since we had a "unique names" requirement (even though the name wasn't the PK), we could make a unique filename for it. I built a text file using VBA Open & Print statements. For each record in the child recordsets, we had a delete flag too. When we visited a record marked for deletion we marked its child records, too. Anyway, as this code looped its way through the child records, we made it a point to print everything from the records in the file, then when we traversed the child records we printed everything about them, too. Afterwards, we deleted every child record we had marked in passing. When we were finished, we deleted the parent record, closed the file, and reported success. Then, once per month, we took all of the files made this way for each of the things requiring this treatment and build a zip file with a name including the year & month plus a prefix of the type of things in that particular file. The zip files, we were planning on keeping forever.

I know my colleagues will know that, but I wanted to show an example of how to do this without losing data due to a cascade deletion.

If I set up my form with a delete flag, is there a way to set a field that can record when that flag was flipped? In other words. If I put a field on the form that says archive this record y/n can I set timestamp to register when that was done? I suppose that if I just set it to stamp when the record was last modified it would do that, hmmm. I may be putting too much thought into this. Seems like ofttimes the best solution is to not get fancy KISS!

If your code follows its own rules, you can simply make the "date of last modification" also be the date of archiving as long as you have a flag that absolutely locks the record from further modification (by your app, of course, since Access doesn't itself permanently lock any records.) So when you set the "last mod" date and the "archived" flag at the same time, that would do what you want as long as you honor the archive flag as a read-only flag for future ops.