Archiving Data Programmatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form which contains several subforms which display the related data
for a project. Once the project is ready to be archived (and removed from
the current DB) the user will click on a CheckBox control. I want to have
everything in related to this ProjectID number in all the tables copy to an
"Archive" copy of the DB. Then I want to delete it from the current project
tables. Any recommendations?
 
You will need to execute a series of action queries to achieve this.

First will be the Append query to add the records to the archive table. Then
you will need to execute another Append query for *each* related table, to
copy those records as well.

Once that's done, you can execute a Delete query for each related table, to
so the records are removed. And finally, execute the delete query to remove
the record from the primary project table.

You probably want to wrap the entire process in a transaction, so you get an
all-or-nothing result, i.e.you don't get a partial copy to the archive table
that did not complete (e.g. because another user or process was editing one
of the records you attempted to delete), or even worse, a delete without a
successful copy.

For guidelines on how to use the transaction, see:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

If you are not sure how to execute the action queries and test the results
so you know whether you need to rollback, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html
 
Immediately after I posted my question, I saw your reply to another person's
question ("taco") from a post on 12/2/2005. That'll teach me to ALWAYS
search first. Thanks for patiently answering the question anyway! I always
appreciate your help.
--
Thanks!
Mona-ABE


Allen Browne said:
You will need to execute a series of action queries to achieve this.

First will be the Append query to add the records to the archive table. Then
you will need to execute another Append query for *each* related table, to
copy those records as well.

Once that's done, you can execute a Delete query for each related table, to
so the records are removed. And finally, execute the delete query to remove
the record from the primary project table.

You probably want to wrap the entire process in a transaction, so you get an
all-or-nothing result, i.e.you don't get a partial copy to the archive table
that did not complete (e.g. because another user or process was editing one
of the records you attempted to delete), or even worse, a delete without a
successful copy.

For guidelines on how to use the transaction, see:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

If you are not sure how to execute the action queries and test the results
so you know whether you need to rollback, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html
Click to expand...
 
Back
Top