Best practices for archiving old records

[sjs94704]

I'm doing DB for an electrical contractor who has 50 guys who go out in the field every day, each one with multiple work orders. I anticipate that the client might be adding lots of records very quickly, so we will need to come up with a business rule for archiving old records.



Question: Is it best to export old records maybe into an external DB when archiving, or is it best to maybe put just a YES/NO flag for IsArchived. If I have this flag only and put in my queries to not include records where that flag is true, does this make my query searches faster, or will the DB only stay faster if I take the records out of the primary back end ?

[pbaldy]

Most of us would recommend the field rather than moving records. If you run into performance problems related to the number of records, then I'd look at moving them, but a Access can handle a lot of records.

[TommyK]

Not sure if this helps here, but I archive records on an annual basis. All my databases have the current year's records in a table labeled 'Current <pick a noun>" Then at the end of each year, I close out the 'current' year's records by saving acopy of the 'current ...' table into a new table called "2014...". Then go back to the 'current...' table, then delete all the records in the original table. It must be done in this order with all objects closed. Renaming the current table to one with the archived year, causes all the queries to rename the source table to the new one. Then reopening a new table for the current year, will give you a table with nothing pointing to it. This allows all the queries, forms and reports to continue to work in the new year without re-writing any code. One thing I do re-do to identify the new years data is to include in the 'current' table a field called "fiscal year". The table defaults to whatever fiscal year is 'current'. I have to go into design mode in the table to redefine the dfault fiscal year'. The data in the previous fiscal year retains that year number, i.e. 2014. If you construct your forms and reports to display the 'fiscal year' field in the report / page header, you always have an assurance you are looking at current data.

For looking up data from previous years, you will need to copy the 'current' queries, reports and forms, then modify the copied objects to look to the archived table. It's been my experience that I don't need all the forms and reports used on the current year for looking up archived data. Just re-create the ones you need.

One could also, instead of creating a new table, every year, just copy it to the end of a new table of all archived data from all previous years. I have never tried that. Then you should only have to re-tool queries, forms and reports for the 'archive' table once.

If you're a bit skittish about deleting an entire table, do what I do when attemtpting to make sweeping changes. Close the database, then in the File Manager, highlight the database, type Ctl+C(opy), then Ctl +P(aste). This creates a duplicate Access database named "Copy of ... <your old database file name>" Make your changes in the copy or the original database. This way if you screw something up, you have a back-up copy.