Suggestions on archiving a database

[animal]

I'm looking for suggestions regarding the archiving of a database. The way the app is now, all of the data they enter is shown on a dbgrid. Now let's say in a year from now they may not want to see data they entered last year, but may on occasion want access to it.

My thought was to include a function which lets them archive data from from a fixed period (ie. calendar year...1999), or let them choose a period. I would then store the database under a different name (ie. 1999.mdb) and recreate the database structure so they would be working with a new and empty DB.

Does anyone have any thoughts or suggestions about this or any other approaches???

 

[MichaelRed]

There are to many variables to generate a universal answer. IF you never need to view the archives with the current, your approach "works" - IF you archive the code/forms/queries along w/ the tables (data). this provides a method to access all of the archived info in some reasonable manner.

Rember that the overall database WILL be dynamic. Table structures will change. this will have a ripple effect on the remainder of the database, so last years 'archive' won't work in this years app.

If you need to review current and archived data then there are a LOT of complications. I can mention a few, however you need to study htis carefully and for a while. then you need to make copies of the currnet and archive db and TEST TEST TEST the functionality.

First, you need some process/mechanisim to 'update' the archived tables to accomodate/include changes to the tables/structures/attributes. Next, you need to modify the current (and archived) tables to be able to clearly discriminate between archive records and current records - and this needs to 'rolled into' all current forms/modules/queries/reports ... Now you get to the "Big Decisions" - are users allowed to "change" the archived data? If so, do you need to track these changes?



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over