I've set up a number of append queries to move records from an Active database to an Archive database. Once the records are moved to the archive, a delete query causes the cascading delete of those records from the Active db.
Now, I also have an append query in my Archive db that can retrieve a record and put it back into the Active db. However, I cannot delete the retrieved record from the archive since it is the primary record for a number of related records (primary table in several one-to-many relationships) that will remain in the archive.
The resulting problem is this: The retrieved record goes back to the Active db, is modified, and has another set of related records attached to it. When I then archive this set, the primary record encounters a key conflict and doesn't append into the archive. All the related records do and there are no problems there.
How do I update the old archived primary record with the modified record when I am archiving it for the 2nd (or greater) time?
The archive DB and active DB are entirely separate databases...not tables in the same db.
Hope this makes sense! Thanks!
[This message has been edited by snorf3 (edited 08-20-2001).]
Now, I also have an append query in my Archive db that can retrieve a record and put it back into the Active db. However, I cannot delete the retrieved record from the archive since it is the primary record for a number of related records (primary table in several one-to-many relationships) that will remain in the archive.
The resulting problem is this: The retrieved record goes back to the Active db, is modified, and has another set of related records attached to it. When I then archive this set, the primary record encounters a key conflict and doesn't append into the archive. All the related records do and there are no problems there.
How do I update the old archived primary record with the modified record when I am archiving it for the 2nd (or greater) time?
The archive DB and active DB are entirely separate databases...not tables in the same db.
Hope this makes sense! Thanks!
[This message has been edited by snorf3 (edited 08-20-2001).]