Archive Access databases regularly

A common Database task archives records from one table to another. If you want to remove unnecessary records from the original table to reduce the size of the table, but you might refer to the records in the future, you can archive records (not delete records). To archive records in the Microsoft®access database, run the query with a macro to move the record from one table to another.

Note: The following procedure uses an example from the Northwind sample database. The default location for the Northwind sample database is the Program Files\Microsoft Office\Office10\Samples folder. If you do not have a Northwind.mdb file in your Samples folder, or if you need more information about opening Northwind, in the Office Assistant or in the Answer Wizard tab of the Help window, type open the Northwind sample database, and then click Search.

Periodically archive records from one table to another

1. Use the copy and Paste commands to copy the table you want to archive to a new table. Copy structure only, do not copy data.

For example, copy the structure of the Products table to a new table named Product archive.

2, create a query that contains all the fields in the Products table, and attach the records to the Product archive table. Set criteria to specify a subset of records to archive.

For example, to create an additional query called product Attach, you can attach an aborted entry only to the Product archive table by setting the criteria cell of the Abort field to-1.

Tip: If the conditions for each archive change, you can create an additional query with a hint parameter so that you can enter different criteria each time you run the query.

3. Create a delete query that has the same condition as the one specified in step 2 for an additional query. You will use this query to remove the records attached in step 2 from the Products table.

For example, create a "product Delete" Delete query that has the same condition as the one specified by the "product Attach" additional query.

Note: If you create an additional query that prompts for parameters, you should also create a delete query that prompts for the same parameters, and make sure to enter the same value for the parameter prompts that appear for each run of the query.

4, create a macro so that first run the additional query, and then run the delete query.

For example, create a macro named Product attach record, run an additional query with the OpenQuery action on the first line of the macro, and run the delete query with the OpenQuery action in the second row of the macro.

5. Run the product attach record macro every time you want to archive records.