Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81

    Unanswered: Archiving Access tables

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

    Any suggestions?

    Thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yes ... Create an archive table but with no PK matching the primary table. Move records over periodically ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •