How do I append a parent table and all related tables to a seperate archive database?

[Dan Landry]

I have a database for tracking personnel and personnel related information. The Parent Table is "ACTIVE PERSONNEL"
which has MemberID as an autonumber. The Child Tables are joined using MemberID and are VACATION, COUNSELING, and REQUESTS each one containing thier own unique ID; VacationID, CounselingID, and RequestID respectively. I am required to keep records of all personnel for 3 years after they transfer, but for the sake of database size I would like to append them to a separate "INACTIVE PERSONNEL" database. I have already created the new database which I imported the table structure of the "ACTIVE PERSONNEL" database into. I am unsure as to how to get all of the information appended to this database when someone transfers. I am relatively new to Access, so feel free to dumb down your answers as much as possible. Thanks for the help

[Dan Landry]

I have a personnel database to keep track of all information relevant to the personnel who are on staff here, and I am required to maintain records for 3 years after someone transfers. I am trying to append data from one parent table and all of it's related child tables to a seperate "Transferred" database so that I can keep the size of my active database down to a reasonable number. I have attached the basic structure of the tables that I will be using. I am VERY new to Access so any help would be greatly appreciated.

[NeoPa]

Firstly, the design cannot be the same in the archive if you have an AutoNumber field in the main table. I'm sure once you think about this you'll see why.

When you have a table design that will allow copying of the main data, then you would need to copy the relevant records from the main set of tables to the Archive ones in an order that is consistent with any restrictions (Referential Integrity etc) set on your data. When successfully done, the same data can then be deleted from the main tables - again in a consistent order.

The code to handle this can be on a form within either of the databases, but whichever it's in, would also need links to the tables of the other in order to do the data transferring.

NB. Restoring data from an archive to tables which have AutoNumber fields is another problem entirely, and is not simple to accomplish. If this is in your future plans then you need to consider this carefully before proceeding.

[NeoPa]

Dan,

I merged your threads as they don't quite have the same question in them, though I'm sure they should have.

Now I know more about your situation (than when I first answered the question) I would suggest strongly that data size is unlikely to be an issue for you. I can't be sure, but unless your enterprise is extremely large (or your database very poorly designed) Access should manage the load.

The recommended approach would be to keep all the records in the same table but with a flag to indicate the status and queries to pull just the relevant data where required.