UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Data Deletion Or Archive, Access 2016    
 
   
MonteyBurns
post Jan 8 2019, 03:32 PM
Post#1



Posts: 64
Joined: 11-June 18



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.)

Or

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.

Montey
Go to the top of the page
 
Doug Steele
post Jan 8 2019, 03:44 PM
Post#2


UtterAccess VIP
Posts: 22,162
Joined: 8-January 07
From: St. Catharines, ON (Canada)


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.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
Jeff B.
post Jan 8 2019, 07:48 PM
Post#3


UtterAccess VIP
Posts: 10,254
Joined: 30-April 10
From: Pacific NorthWet


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...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
MonteyBurns
post Jan 9 2019, 07:40 PM
Post#4



Posts: 64
Joined: 11-June 18



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.

Montey
Go to the top of the page
 
tina t
post Jan 9 2019, 11:21 PM
Post#5



Posts: 5,955
Joined: 11-November 10
From: SoCal, USA


QUOTE
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...

hth
tina
This post has been edited by tina t: Jan 9 2019, 11:23 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Jeff B.
post Jan 10 2019, 08:30 AM
Post#6


UtterAccess VIP
Posts: 10,254
Joined: 30-April 10
From: Pacific NorthWet


<Montey>

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.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
MonteyBurns
post Jan 10 2019, 07:02 PM
Post#7



Posts: 64
Joined: 11-June 18



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.

Montey
Go to the top of the page
 
tina t
post Jan 10 2019, 08:01 PM
Post#8



Posts: 5,955
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th June 2019 - 08:00 PM