Archive Access data

You can help keep your data organized by periodically archiving old or inactive records. For example, suppose you use Access to manage a lending library. You might have a table that tracks lending transactions, with a field containing the date the item was borrowed and a field containing the date it was returned. Perhaps your record retention policy is to archive lending transaction records one year after the item's return, and to destroy the records after two years.

You can archive all the tables in a database, specific tables, or only specific records — for example, records that are older than a particular date. This topic explains how to archive data.

In this article

Overview

Periodically move records into an archive table

Periodically replace a table

Periodically replace all tables

Overview

You should consider archiving data when you have data that you do not plan to use anymore, and you want to keep the data handy in case you do need it. You might have a policy that guides your archive plans, such as a data retention policy.

Archiving is often used to store data based on a date condition, such as the end of a month arriving.

Ways to archive data

The following table lists ways to archive data, when you should use each method, and other considerations.

Method

Description

Use when...

Other considerations

Periodically move records into an archive table

Periodically, you run a query that selects the data that you want to archive and appends the data to an archive table. Then, you run a query that selects the same data (from the original table) and deletes it.

Some of the records in a table meet the condition that you use to determine whether to archive.

EXAMPLE: You want to archive library transactions (check outs) if the Checked In Date is at least a year old.

  • May have to work around referential integrity, especially if the records that you want to archive are on the one- side of a one-to-many relationship.

  • If you need to use parameters in your queries, you should create a form to handle the parameters. Otherwise, you risk data loss.

Periodically replace a table

Periodically, you make an archive copy of a particular table, and then replace the table with a new, empty copy.

All the records in a table meet the condition that you use to determine whether to archive.

EXAMPLE: You have a table that stores daily temperature extremes. Every year, you archive the table and start fresh with an empty one.

  • May have to work around referential integrity.

Periodically replace all tables

You start with a split database (a database comprising: a back-end database file that contains all the tables; and a front-end database file that contains all the other database objects). Periodically, you make an archive copy of the back-end database, and then replace the back-end database with a new, empty one.

All the records in most of the tables in your database meet the condition you use to determine whether to archive.

EXAMPLE: Your database consists of several tables with different kinds of meteorological data. Each year, you archive all of the tables.

  • If you have any lookup tables (tables that you use to store values for looking up, such postal codes or departments), you may need to import their data into the new back-end database.

  • You cannot use a macro for this method. You must archive the database manually.

Working around referential integrity

If the records that you want to archive are related to records in other tables, you may have to work around the relationship. If the records that you want to archive are "child" records (they belong to the "many" side of a one-to-many relationship), you can probably safely archive them without concern. If the records that you want to archive are "parent" records (they belong to the "one" side of a one-to-many relationship), when you archive them their related "child" records may:

  • Prevent you from deleting the "parent" records. This could cause problems if you have already appended the "parent" records to the archive table.

    - or -

  • Become "orphans" — records that belong to a "parent" that does not exist. This could cause problems with data integrity and various functionality in your database that uses the "orphan" records.

To account for referential integrity, take the following steps:

  1. Determine what "child" records belong to the records that you want to archive. For example, if I want to archive records of assets that I lend at my library, I first determine whether there are any transactions open on those assets, i.e., whether the assets have been checked out but not returned.

  2. Do one of the following:

    • If the "child" records can always be safely deleted, make sure that the relationship enforces referential integrity, with cascading deletes. This ensures that all related "child" records will be deleted.

    • If the "child" records cannot always be safely deleted, consider archiving all the tables in the database.

    • Create a query that selects "parent" records that don't have "child" records. Then, use that first query to create your archive queries (see the section Periodically move records into an archive table), instead of using the "parent" table.

      Tip: You can use the Query Wizard to find unmatched records. For more information, see the article Compare two tables and find records without matches.

Top of Page

Periodically move records into an archive table

First, you make an empty copy of the table that has records that you want to archive. This new empty table is your archive table. Next, you create an append query to copy records from the original table to the archive table. Then, you create a delete query to remove the archived records from the original table. Finally, you create a macro that runs both queries. When you want to archive records, you run the macro.

Tip: You can attach a macro to a button on a form, so that when the button is clicked, the macro runs.

Step 1: Create an archive table

Step 2: Create a query that copies data to your archive table

Step 3: Create a delete query that removes the same data from your original table

Step 4: Create a macro that runs both queries

Step 1: Create an archive table

If you want to keep all your archived records in one table, you only do this step once. The archive table that you create in this step will hold all of your archived records.

If you want to destroy your old archive table when you create a new one, you do not need to do this step. You can use a make-table query instead of an append query to copy the data to your archive table. To do this, skip ahead to Step 2.

If you want to use a new archive table every time that you archive, and you want to keep your old archive tables, rename your old archive table before you create a new one. If you are archiving based on a date, considering naming your old archive tables according to the date range they represent.

Note: If you create a new archive table every time and keep the old one, you must rename the old archive before you run your queries.

  1. In the Navigation Pane, select the table that has records to archive, press CTRL+C, and then press CTRL+V.

  2. In the Paste Table As dialog box, under Paste Options, select Structure Only.

  3. In the Table Name box, delete the words Copy Of and append an underscore and the word "archive" to the existing table name, and then click OK. For example, if the original table is named Transactions the archive table is named Transactions_archive.

    Paste Table As

Step 2: Create a query that copies data to your archive table

  1. On the Create tab, in the Other group, click Query Design.

  2. Use the Show Table dialog box to add the table that has records that you want to archive. Close the Show Table dialog box.

  3. In the query design window, double-click the asterisk (*) in the table that you just added. The table name and an asterisk appear in the first column of the query design grid.

    Note: The asterisk indicates that the query should include all of the fields from the table in the query output. When you use the asterisk, if fields are added or removed from the table, the query output adjusts accordingly.

  4. In the query design window, double-click the field that you want to use to specify a condition that records should meet before you archive them. For example, if your Transactions table has a field called Checked In Date and you want to archive all records where that date is more than a year old, you double-click Checked in Date. The field appears in the next empty column of the query design grid.

    Query deisgn grid

    Repeat this step if you want to use criteria with additional fields.

  5. Use the Criteria row to specify criteria for the fields that you just added. For example, you might specify that the Checked In Date must be earlier than January 1st, 2008 by using the expression <#1/1/2008# in the Criteria row.

    If your criteria values change each time that you archive, you should make your query ask for input. To do this, you use a parameter in the Criteria row so that the query asks for input. To use a parameter, use an expression as you normally would, but instead of a specified value, use a brief question surrounded by square brackets. For example, you might use the expression <[Archive transactions completed before:], like so:

    Parameter expression

    For more information about using parameters, see the article Make a query ask for input.

    You might also want to use the or row to specify alternate conditions. For more information about using criteria, see the article Examples of query criteria.

    Tip: If you are using a date field to specify criteria, and you want to archive all records that are older than the current date, enter <Date() in the Criteria row for the date field.

  6. Do one of the following:

    • If you have already created the archive table, you need an append query to add the specified records to that table.

      1. On the Design tab, in the Query Type group, click Append.

      2. In the Append dialog box, in the Table Name box, select the name of the archive table, and then click OK.

        Append To

        In the query design grid, the Append To row appears.

      3. Clear the Append To row for any fields that you used to specify criteria. (Only the asterisk should have a value for Append To.)

        Query design grid Append To row

    • If you have not created the archive table, you need a make-table query to create the archive table by using the specified records.

      1. On the Design tab, in the Query Type group, click Make Table.

      2. In the Make-Table dialog box, in the Table Name box, type the name of the archive table, and then click OK.

  7. Press CTRL+S to save the query.

Step 3: Create a delete query that removes the same data from your original table

  1. On the Create tab, in the Other group, click Query Design.

  2. Use the Show Table dialog box to add the table that has records that you want to archive. Close the Show Table dialog box.

  3. In the query design window, double-click the asterisk (*) in the table that you just added. The table name and an asterisk appears in the first column of the query design grid.

  4. In the query design window, double-click the same fields that you used to specify a condition in the append query.

  5. Use the Criteria row to specify criteria for the fields that you just added. For more information about using criteria, see the article Examples of query criteria.

    Important: If your append or make-table query used a parameter, so should your delete query. Moreover, you should make sure that you enter the same value for both queries. If you enter different parameter values, you may lose data. To help prevent data loss, consider using a form to collect the values, and making the queries ask the form for the input values. For more information, see the article Make a query ask for input.

  6. On the Design tab, in the Query Type group, click Delete.

    The Delete row appears in the query design grid.

    Query design grid Delete row

  7. Press CTRL+S to save the query.

Step 4: Create a macro that runs both queries

  1. On the Create tab, in the Other group, click Macro. If this command is unavailable, click the arrow beneath either the Module or the Class Module button, and then click Macro.

  2. In the first row of the macro design grid, in the Action column, select OpenQuery.

    Open Query macro action

  3. At the bottom of the grid, under Action Arguments, in the Query Name box, select the (append or make-table) query that you created in Step 2 from the drop-down list.

    Open Query macro action Query argument

  4. In the second row of the macro design grid, in the Action column, select OpenQuery.

  5. At the bottom of the grid, under Action Arguments, in the Query Name box, select the delete query from the drop-down list.

    Your macro should look something like this:

    macro ready to save

  6. Press CTRL+S to save the macro.

    When you want to archive records, run the macro.

Top of Page

Periodically replace a table

If you always want to archive all the data in a table, you don't have to use queries or macros. As an alternative, you can periodically replace the table with an empty copy.

Important: If the table that you archive is related to other tables, you might need to work around referential integrity.

  1. In the Navigation Pane, select the table that you want to archive, press CTRL+C, and then press CTRL+V.

  2. In the Paste Table As dialog box, under Paste Options, select Structure Only, and then click OK.

    Access names the copy Copy Of original table name.

  3. In the Navigation Pane, right-click the original table and then click Rename on the shortcut menu that appears.

    Give the table a name that indicates what it contains, such as "DailyTemperatureExtremes_archive_2007".

  4. In the Navigation Pane, right-click the empty copy and then click Rename on the shortcut menu that appears.

    Give the empty copy the original table name.

Top of Page

Periodically replace all tables

If you use a split database, you can periodically replace all tables by replacing the back-end database with an empty copy.

First, you prepare the empty copy. Unless your database design changes, you can reuse this copy each time that you archive. Then, when you want to archive, you rename the existing back-end database to indicate that it is an archive, and then save the empty copy as the new back-end database.

Prepare an empty copy of a back-end database

Import the table definitions for all the tables in the back-end database.

  1. Create a new, blank database. Delete Table1.

  2. On the External Data tab, in the Import group, click Access.

  3. In the Get External Data dialog box, select Import tables, queries, forms, reports, macros and modules into the current database, and then click Browse.

  4. In the File Open dialog box, select the back-end database. Click Open to close the File Open dialog box, and then click OK.

  5. In the Import Objects dialog box, click Options.

  6. Under Import Tables, select Definition Only.

  7. On the Tables tab, click Select All, click OK, and then click Close.

Next, add data to any lookup tables in the empty copy. For each lookup table, do the following steps:

  1. Link to the lookup table in the existing back-end database.

  2. Create an append query that adds all records from the original to the copy.

Replace the back-end database with an empty copy

First, rename the existing back-end database to indicate that it is now an archive.

Then, open the empty copy, and save it by using the original back-end database name.

  1. Click the Microsoft Office Button Office button image , and then point to Save As. The Save As menu opens.

  2. Under Save the database in another format, click Access 2007 Database.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×