I am trying to find the simplest way to periodically archive old records from a database. The autonumber field value of the main table is used as the main ID NUmber (Call-ID) which is contained in other tables. (Most other tables have many occurrences of this one ID # whereas it is unique in themaster table.)
I could set up an archive version of the back and front ends, however I want to preserve the autonumbers. (If I use append queries I think it will renumber them?) The numbers must be preserved--they are extensively used, like invoice #s. I would rather not do additional design by adding a new fields--ideally the archive mdb will be as similar as possible to the original (allowing easier use of another front end with it, etc.)
One possibility I guess is to turn off autonumber in the table design in the archive database.
Due to the number of records I was thinking of creating a new archive database every quarter or year.
Alternatively I could set up a separate mdb file to archive each separate table. (there are at least 10 tables) This would be harder to set up a read-only front end for but would prevent setting up a new archive back & front end each quarter (or year) when old records are moved out.
A key consideration for me is doing this as simply as possible because once I set up the procedure there may be other staff who implement it in future quarters. These people don't know VBA and are not as expert as I am in Access, ranges from newbies to medium expertise (designing simpler, less relational, schemas only).