Walter said:
I have built an Append Query that will copy records older than 180
days to a new table called Archive.
I have also built a query that deletes the same records from the
current table.
Is there a way to do this all at once or do I have to run both
queries. I am using it to Archive older records into a separate
table.
It would ne much better to simply add a Yes/No field that marks records as
"inactive" or "archived" and then filter those rows out of your normal
processes. Archiving seldom accomplishes much of anything and in the few cases
where it makes sense it would be to make "before" copies of changed records.
Another case where it can be justified is if you need to move the data to
another *file* to prevent your primary file from exceeding the size limit for
Access. Moving it to another table within the same file has little benefit that
I can see.
Another advantage of using a flag field is that on those occasions where you
want to query across active and inactive records it is easy because they are all
still in one table.
If I were going to do the "move" thing I would execute both queries from code
within a transaction. You don't want the copy query to fail and then still
execute the delete query as now the records are gone.
dbEngine.BeginTransaction
CurrentDB.Execute "AppendQueryName", dbFailOnError
CurrentDB.Execute "DeleteQueryName, dbFailOnError
dbEngine.CommitTransaction
This would be wrapped in an error handler with the error trap issuing a Rollback
on the transaction.
