Archive (1 Viewer)

Danny

Registered User.
Local time
Today, 06:31
Joined
Jul 31, 2002
Messages
140
Greetings,

A former employee developed a case tracking (ms access 2016 db) that is getting bigger in size. The management inquire about archiving those cases (along with attachments) that were 'closed' prior to 2016 onto a removable HD and delete them from the DB. Moving forward, do the same task every couple of years.

The DB is split (be/fe). Can you please assist the best approach to accomplish this?

TIA

Regards,
 

isladogs

MVP / VIP
Local time
Today, 10:31
Joined
Jan 14, 2017
Messages
18,186
Based on personal experience, my advice is not to do so
You are likely to make a lot of work for yourself in the future when you need to retrieve archived data.
If the BE is in danger of reaching the Access limit of 2GB, then do one of the following:
1. Split the BE into more than one file. The Access FE can link t multiple BEs
2. Upsize the BE to SQL Server which is more stable and can handle larger files. The free Express version can handle files up to 10GB and paid version much more than that
Code:
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:31
Joined
Feb 28, 2001
Messages
26,996
And while I agree with Colin that it might be better to keep things online, the idea of having a secondary archiving BE file was what worked for us.

If that is the way you wanted to go, you would have to define a BE file that contained a table of the same structure & description as the one you are going to purge. Then write two queries - one to do an INSERT INTO of all the fields from the main table to the corresponding fields in the archive table ... "WHERE status='Closed' AND closedate < #1/1/2017#" (or something like that). Then create a "DELETE * FROM maintable"... with the same WHERE clause.

Thereafter, your archival research might need to work with a UNION query between the main table and the archived table. This is an overview of the approach and is an alternate opinion to Colin's - but I strongly emphasize he is NOT wrong.

What will decide which way to go for you will be your business rules, not our viewpoints.
 

isladogs

MVP / VIP
Local time
Today, 10:31
Joined
Jan 14, 2017
Messages
18,186
I totally agree with the doc about making the decision that best fits your needs.
Perhaps it would be a good idea to explain why I recommended not to do this

Many years ago when I was young and naïve (ok just naïve), I made a decision with one of my schools databases to separate all student leavers data into separate tables And there were a lot of tables. We then made another decision to archive all data for previous academic years into separate tables as well...which of course meant archiving all leavers tables for previous years.
The net result of those decisions was four sets of tables instead of one set.
Of course that also meant four sets of queries and in many cases four sets of forms and reports plus all the additional code that went with those.
Every development process from then on took that much longer as a result

We rarely recombined data using union queries as it was unusual for us ever to need more than one category of the data at a time

The screenshot below is from a database statistics thread in the code repository https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=68466&d=1510279805



The left side of the screenshot shows the huge number of database objects that resulted from those decisions. After a few years, the BE SQL database was approx. 3GB

Of course, it still worked perfectly.
Searches were generally a bit faster because there were fewer records in each table than if they had remained combined.

However, in all subsequent databases we learned from our mistake and made the opposite decision. Instead, we kept the data combined but adding a boolean field to indicate current students or leavers and another field to indicate the academic year for that data.
As a result, development time was much reduced and both FE and BE were also smaller
 
Last edited:

MarkK

bit cruncher
Local time
Today, 03:31
Joined
Mar 17, 2004
Messages
8,178
Conceptually, a database IS an archive. Moving data out of it, to archive it again, seems like a make-work project that only harms your ability to make a historical study of your business patterns.
IMO, and in agreement with others, don't do it.
Mark
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:31
Joined
Feb 19, 2002
Messages
42,970
Your active database should only contain data that is needed for current and historical reporting. Once data is no longer of interest for reporting, it makes sense to archive it to get it out of the way. The purpose of the archive is to allow you to research problems. If your tables contain attachments, they are larger than they need to be and archiving is much more important. Most people don't include documents in the actual tables but instead keep a text field with a path that allows them to be opened when needed.

Whether to archive or not almost always revolves around space requirements. The best option is to keep all the data in a single database so only archive if you truly need to reduce the size of your active database.
 

Danny

Registered User.
Local time
Today, 06:31
Joined
Jul 31, 2002
Messages
140
Thank you everyone for your input to my question.

Colin, I was informed the method you outlined, and upsizing to SQL was discussed before I got here. But, it was decided to go with archiving instead for now and look into replacing ms access DB with another more robust case management DB.

Doc,
If that is the way you wanted to go, you would have to define a BE file that contained a table of the same structure & description as the one you are going to purge.

The DB is called PICDB and the table is called PData to include providers and recipients records that need to be archived (based on a given year)
Just to make sure, I’m doing this correct:

So, I’ll copy tblPData > paste > call it tblPData_archive_072318 >choose structure only... and, write the two quaries?

Pat,
The best option is to keep all the data in a single database so only archive if you truly need to reduce the size of your active database.

We are truly at a stage to reduce the size of our database. The management is looking to replace the current ms access to a more robust case management system. Microsoft Dynamics brought up into the discussion.

TIA

Regards,
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:31
Joined
Feb 28, 2001
Messages
26,996
You certainly COULD do a copy/paste-structure as one way to create the tables. But a step you didn't mention is this: In the archive database, rename the table so you know it is the archive table. Then LINK that table to your FE.

Now you can write the query to do the INSERT INTO and the DELETE operations that drive directly from the FE because BOTH tables are visible to the FE. And if you make a union query for special archived data research, you can see all of the non-archived data and all of the archived data in the same query. There are limits to how far this can go, but you could easily get up to three or four separate BE files for older and older archives.

Now, the good part of doing it this way is that you would rarely or NEVER update anything in the archived BE files, nor delete from the archive. That means that you would almost never have to compact & repair them either.
 

Danny

Registered User.
Local time
Today, 06:31
Joined
Jul 31, 2002
Messages
140
Thanks DOC,
But a step you didn't mention is this: In the archive database, rename the table so you know it is the archive table.
I thought I included the renaming part in my previous post
I’ll copy tblPData > paste > call it tblPData_archive_072318 >choose structure only...
Please let me know if I miss any steps.
Also, the INSERT INTO query is to populate the data to the copied (structure only) archived table, and the DELETE query is to remove old data from the original table, correct?

TIA

Regards,
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:31
Joined
Feb 28, 2001
Messages
26,996
Also, the INSERT INTO query is to populate the data to the copied (structure only) archived table, and the DELETE query is to remove old data from the original table, correct?

That would do it for you, I think.
 

Users who are viewing this thread

Top Bottom