Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Interface Design _ Data Deletion Or Archive

Posted by: MonteyBurns Jan 8 2019, 03:32 PM

Happy New Year to all you U.A. Members !!!

Well I am still plugging away on a couple of databases and I'm working on trying to find a good and efficient way of "archiving" records.
Would you create one table with all the fields? For example; TBL_ArchivedRecords - then all the field names (Company_ID, CompanyName, Employee_ID, EmpFirstName, EmpLastName, etc.)


Would you create an Archive table version of an existing table?
For example; you would have TBL_CompanyInfo, TBL_EmployeeInfo, therefore you would create TBL_ARCHIVEDCompanyInfo, TBL_ARCHIVEDEmployeeInfo, etc.
Therefore, when you want to "Archive" the data say every year, you would run a query to find record sets over a year and move them to the ARCHIVED TBL.

For now, I am thinking of doing this with a macro on a maintenance button of some kind, but I would like to get some information on how to handle this.
The people I am doing this for would like to retain the records for 3 years and then purge (delete) them from the database but I am not quite sure on how to handle this yet and what needs to be in place for this kind of feature.

Any help would be appreciated.

Thanks in advance.


Posted by: Doug Steele Jan 8 2019, 03:44 PM

How much data are we talking? Unless it's thousands of rows, why not just keep it in the same tables?

If you've got a legitimate reason, I'd say simply make a copy of the backend database, then delete the rows that are no longer required from the active one.

Posted by: Jeff B. Jan 8 2019, 07:48 PM

Different folks mean different things by "archive". Do you mean "don't see it when I open a form but the data's still there if I need it"? Do you mean "gone, deleted, never to be needed"? Do you mean "saved somewhere so I can look it up later, but with some extra work"?

More info, please...

Posted by: MonteyBurns Jan 9 2019, 07:40 PM

Sorry for the delay in responding and thanks for the replies.

Doug: The amount of data for a 3 year period would roughly be around 2500 to 3000 records. The back end copy and paste is a good idea but the users wouldn't be comfortable with that and some of the data would still be required.

Jeff B: Essentially that's what the question is supposed to help me decide. Which method do you experienced developers use? Why? What is the structure to set it up? What are the pitfalls to watch for? My database will be on a Network shared folder with about 3 to 5 users periodically using it. What method would work best in that environment.

The users would prefer to permanently delete records after 3 years. The knowledge I've gained about databases have all said "deleting records is not a good practice". So I'm trying to determine which method to use and how to set it up. I'm leaning towards Jeff B.'s option
"saved somewhere so I can look it up later, but with some extra work".

Thanks in advance for any responses.


Posted by: tina t Jan 9 2019, 11:21 PM

The amount of data for a 3 year period would roughly be around 2500 to 3000 records.

well, in general terms, that's tiny for Access. the exception would be if you're storing objects (like files) or something else in the tables, that would make the overall size of the database huge. i mean, from the standpoint of number of records to handle, it still probably wouldn't be a problem, but you'd run into the "limit to size of database file" issue at some point.

my suggestion would be to not archive any of the records at all. assuming that the data tables include a date field - which i assume they must, else how would you identify records over a year old - you can set reports and forms to only show records that belong to a specified time frame, easily enough. looking at current records, or at older records, or at all records, then becomes a result of what time frame the user chooses at any point. there are many variations of this concept that you can build into the user interface, in order to make data available (or not), and display it, to suit the needs of your customer.

as for deleting records completely, well, i personally hate to delete data. as soon as i do, sure enough, here comes a manager wanting to see back-data from who-knows-when. at a thousand records a year, or less - shoot, you could keep records going back decades without having to remove any records from the "live" data tables (assuming no file size issue, of course). but if the customer really really really wants the data deleted entirely after x amount of time, you could set up a series of Delete queries as needed to clean up the data tables, criteria to pull all records with date field < a specified date, and then just run the queries once a year, and backing up the db first, of course, just in case...


Posted by: Jeff B. Jan 10 2019, 08:30 AM


Actually, my preference/practice is to include an [EndEffectiveDate] field, and use a query to display only those records that have no [EndEffectiveDate] or a date in the future. That way, I can always 'duck behind the curtain' and create a query that retrieves old/archived data without having to resort to looking in other dbs.

Posted by: MonteyBurns Jan 10 2019, 07:02 PM

Tina T: Very good idea. I never really thought of it like that and I like that idea. I know the volume of data is very low and by the time they reach a level of where they need to worry about the database size, they will have likely moved on to a different form of data management.

Jeff B: That is a good way of dealing with data as well. I like that idea too.

Thank you both for your responses. notworthy.gif
These are exactly the kind of responses I was looking for. When you are starting out, it sometimes gets difficult to see the trees through the forest. Sometimes I tend to over think and over complicate things.


Posted by: tina t Jan 10 2019, 08:01 PM

you're welcome, Montey, we're all happy to help. :) tina