setting up DB + Archive DB

[krowe]

Hi

I have a database that i need to archive and start a fresh as i'm integrating with some other software. My plan is this:

1. copy database AdviceDB back end and rename ArchiveDB
2. delete data in AdviceDB back end
3. copy front end and link tables of the new copy to ArchiveDB

so i will then have 2 back ends a 2 front ends.

I then need to be able to retrieve a record from the archive when required. The record will need to get info from several tables (all linked by personID).

I'm not sure where to start with this task. my idea so far is to put linked tables in the AdviceDB front end to the archive DB (so i can search the archives) then have a button with some vba to copy across the records to the new tables using a where statement.

I start to lose my mind when i consider how i can can create a new PersonID and make sure the data from the archive tables is all translated to reflect the new PersonID. (each table has PersonID in to create the links with the main tblPerson).

Does anyone have any experience doing this, if so could i have some pointers please?

Thanks

 

[plog]

Why do you need an archive database? Why can't the data remain where it is? It sounds like you still need the data in the archive data regularly which defeats the purpose of archiving.

Again, why must you archive?

 

[krowe]

There is a lot of bad data that will cause problems with another database I am linking too. Id rather start with a clean sheet and pull data across if and when I need it, cleaning up each record. Otherwise I have 1000's of records to check and clean and only 3 weeks to do it

 

[krowe]

i have this sorted now, i found out a way to start my indexing on thetblPerson on the new database at a higher number (i will start at 50,000). I can then append the old records without conflict. The the related tables will be fine too as it doesn't matter if their unique referneces change, so long as PersonID remains the same in all of them.