Archiving records

[Maz Ten]

I am currently archiving records to another table by creating an Append
query then a Delete query.
The Append query appends the required data to the archive table.
The Delete query then deletes the archived records from the original table.
This all works fine but I have to do that procedure 6 times
first for the main table data then for all the related tables.

Is there an easier way to archive records wether they are related tables or
not.

Thanks in advance

Maz T

 

[Sal Rosario]

Let's see what the experts say, but have you thought about creating a macro
or VBA code so that you can run the macro/code and have all these queries
executed so you don't have to do it yourself? Just a thought!

Sal
www.cedrostec.com

 

[Maz Ten]

Thanks for your answer.
Yes I have but there must me an easier way

Maz

 

[Duane Hookom]

Is there a reason you are archiving? Access can easily handle 100,000s
records under the right circumstance.

If you want to keep some records and archive some records I don't think
there is an easier method. I assume you have automated this with code that
considers some date/time field(s).

 

[John Vinson]

Thanks for your answer.
Yes I have but there must me an easier way

VBA code is actually probably the easiest, and certainly the safest,
way. If you'ld like some help writing it post back.

 

[Allen Browne]

For an example of how to execute a copy and delete in a transaction, see
article:
Archive: Move records to another table
at:
http://users.bigpond.net.au/abrowne1/ser-37.html

Your original question mentioned the possibiltiy of cascading deletes. You
would need to copy the related records also before executing the query that
could cause the cacading deletes.

 

[John Vinson]

For an example of how to execute a copy and delete in a transaction, see
article:
Archive: Move records to another table
at:
http://users.bigpond.net.au/abrowne1/ser-37.html

Your original question mentioned the possibiltiy of cascading deletes. You
would need to copy the related records also before executing the query that
could cause the cacading deletes.

Thanks Alan - bookmarked; that's a lot better than what I tossed
together!