Append/update queries

[snorf3]

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

 

[Pat Hartman]

Look up the following article in the Microsoft knowledge base:

Q127977 - ACC Updating and Appending Records in One Update Query http://support.microsoft.com/support/c.asp?FR=0