Solved Archiving records instead of deleting

[LarryE]

Over the years I have been a member here, many people have recommended "archiving" records rather than deleting them. I have never done that but want to experiment with it. So, I will ask how should you "archive" a record? As far as I can tell. there may be two ways:

1. Append the record to an "archive" table and then delete it from its normal table?
2. Create an "Archive" field in the table using a Yes/No Data Type and just leave it showing as "Archived", then disable the ability to change the record using VBA code?

If anyone has done this, I would sure like to know how you did it. I always enforce RI, so I am curious if this process can affect RI and if so, how.
For example, I developed a simple project that tracks school room supply quantities on hand. The design looks like this:

Each Classroom may have multiple Halls and each Category may have multiple Supplies. They come together in a SupplyQuantity junction table.

1. I have a Category of Supply named "Test Category" in the Category table.
2. There is a single related record in the Supply table called "Test Supply"
3. The "Test Supply" record has 4 related records in the SupplyQuantity table. One Quantity for each of 4 Classroom and Hall records.

How would you "Archive" the "Test Category" in the Category table? And what happens to the related records in the Supply and SupplyQuantity tables?

Thank you in advance for any suggestions.

 

[Pat Hartman]

The data stays where it is. You add an archivedDate which gives you more information than a simple flag. The problem with adding this feature after the fact is that it will change most queries because you generally don't want to include archived records. The update forms, will need to show archived records so there you might need an option that allows it to show archived records or only active records or both.

You probably wouldn't archive the Category, you would be more likely to archive the Supply item but it doesn't matter. You would include all 5 tables in the query along with the archivedDate and the query would include or exclude archived items depending on where you will use the query.

In forms, controls bound to combos need to include the archived date field. I sort the items so that the archived items sort to the bottom so they are still in the RowSource, just out of the way. When the user selects an item, the code looks at the archivedDate and disallows the selection if the item is archived. Existing records are not affected. They still show the archived item. If it is relevant, you can also show an archived flag on the form. Date isn't usually important in this context but you could show the date if you prefer.

 

[LarryE]

The data stays where it is. You add an archivedDate which gives you more information than a simple flag. The problem with adding this feature after the fact is that it will change most queries because you generally don't want to include archived records. The update forms, will need to show archived records so there you might need an option that allows it to show archived records or only active records or both.

You probably wouldn't archive the Category, you would be more likely to archive the Supply item but it doesn't matter. You would include all 5 tables in the query along with the archivedDate and the query would include or exclude archived items depending on where you will use the query.

In forms, controls bound to combos need to include the archived date field. I sort the items so that the archived items sort to the bottom so they are still in the RowSource, just out of the way. When the user selects an item, the code looks at the archivedDate and disallows the selection if the item is archived. Existing records are not affected. They still show the archived item. If it is relevant, you can also show an archived flag on the form. Date isn't usually important in this context but you could show the date if you prefer.

Thanks a lot Pat. I think I'll use an Option Group control on the form header to "Show Archived" or "Show Active". Then I can check or uncheck them at will. It's just an experiment at this point. But I was curious to know if I should use it for the future. I'll mark it as solved for now. Thanks again.

 

[Pat Hartman]

You're welcome. It is hard to add to an existing database but easy enough if you are building from scratch.

 

[LarryE]

You're welcome. It is hard to add to an existing database but easy enough if you are building from scratch.

I just have an "Archive" checkbox control on the form and then filter the form based upon Archive=True or False. An Option Group selection with "Archive" or "Active" choices triggers the filter. But you need to be careful that every other form, query and report is checked and modified. So, there is more to it than one would think.

 

[isladogs]

Just to reinforce the earlier response.
Never ever transfer the archived data to another table - it just creates significant additional maintenance for absolutely no gain
You will end up with many additional tables & queries each of which will need updating if the original table structure changes

I say this from experience of making the wrong decision in my first production database. It cost me many hundreds of hours of additional work

 

[LarryE]

Just to reinforce the earlier response.
Never ever transfer the archived data to another table - it just creates significant additional maintenance for absolutely no gain
You will end up with many additional tables & queries each of which will need updating if the original table structure changes

I say this from experience of making the wrong decision in my first production database. It cost me many hundreds of hours of additional work

I am sure that is correct. At this point I am just experimenting. Just filtering archived records out and in on the form and letting native ACCESS RI work. I'll see how it goes and if there is any real benefit other than accidently deleting a record. I have a confirmation message for that anyway. I would like to hear anyone's comments on the benefits of archiving versus deleting. Thanks Colin.

 

[Pat Hartman]

I would like to hear anyone's comments on the benefits of archiving versus deleting.

I archive in tables where it is necessary to keep history and allow deletes when it doesn't matter. So client records get archived but items on an order can be deleted up until the order ships. Once the order ships, deletes are not allowed. Obviously, related records can never be deleted.

 

[isladogs]

Just to be clear, I archive data but do so by setting a 'flag'. The data remains in the same table and is normally filtered out (or in when appropriate)

 

[LarryE]

I archive in tables where it is necessary to keep history and allow deletes when it doesn't matter

That is a very good point. Maybe a choice depending on the kind of record it is and where it is used. Coming from an internal audit banking background, I know certain records were required to be maintained for certain periods of time and made accessible to regulators and to us to review historical trends. Not the case here, but a point to be considered for sure.

 

[LarryE]

Just to be clear, I archive data but do so by setting a 'flag'. The data remains in the same table and is normally filtered out (or in when appropriate)

Exactly what I'm doing.

 

[RivenSkaye]

In situations where there's an administrative need to keep data for a while, e.g. client data after their business has ended, using a nullable DateTime to flag archival has a lot of benefits as well. It allows you to select `WHERE Archived IS [NOT] NULL` depending on which you want to show, as well as being able to automate deletes for any rows older than the required timespan

 

[Pat Hartman]

using a nullable DateTime to flag archival has a lot of benefits as well

That is what I said in my first post at #2. A y/n flag doesn't provide much in the way of information. Just because you are archiving data in place doesn't mean that you never want to purge it. You can only purge if you use a datetime field for your archive indicator rather than a y/n field.

Also, keep in mind that you probably want to run one or more update queries to update child records when a parent is archived. And of course, you need to have an undo process where you mark an archived record active again.