Archive Access data

You can keep your Microsoft Access databases organized and manageable by periodically archiving old or inactive records. 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 use three different options to archive the data in your Access database.

In this article

When should you consider archiving?

Periodically replace a table

Periodically replace all tables

Periodically move records into an archive table

When should you consider archiving?

When a database on your computer contains data that you do not plan to use anymore, but you want to keep that data handy in case you do need it at some point in time, or to meet a data retention policy, archiving is a good way to retain such data. Archiving is also often used to keep data based on a date condition, such as at the end of a month.

Ways to archive data

The following table lists methods to archive data, a description of the method, explanation of when you should use each method, and other specific considerations for the method.

Method

Description

Use when...

Other considerations

Periodically replace a table

Periodically makes an archive copy of a particular table and replaces that table with a new, empty table copy.

All the records in a table meet the conditions 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.

Periodically replace all tables

Periodically make an archive copy of the back-end database, and then replace the back-end database with a new, empty one. Requires 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).

All the records in most of the tables in a 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 that data into the new back-end database.

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

Periodically move records into an archive table

Periodically runs a query that selects the records for archiving, and adds the data to an archive table, and you then run a query to selects the same records (from the original table) and delete them.

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. See the section on Working around referential integrity for more information.

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

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 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, to archive records of assets that are lent at a library, 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.

Top of Page

Periodically replace a table

If you want to archive all the data in a table, 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 tables 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 as a Copy Of original table name.

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

Give the table another name to indicate what it contains, such as "DailyTemperatureExtremes_archive_2019".

  1. In the Navigation Pane, right-click the empty copy, and then click Rename on the shortcut menu. Rename it with the original table’s 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.

To do this, first prepare the empty copy. Unless the design of your database changes, you can reuse this empty copy each time that you archive. To archive just rename the existing back-end database to indicate that it is an archive, and save the empty copy as the new back-end database.

Prepare an empty copy of a back-end database

First, import the table definitions for all the tables in the back-end database.

  1. On the File tab click New, select Blank database, and then click Create.

  2. Close Table1.

  3. On the External Data tab, in the Import & Link group, click Access.

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

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

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

  7. Under Import Tables, select Definition Only.

  8. 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 File tab, and then click Save Database As. You may be prompted to close all open objects; if so click OK. The Save As dialog box opens.

  2. In the Save in box (at the top of the Save As dialog box), make sure that you are saving the file in the same place as the original back-end database.

  3. In the File Name box, enter the name of the original back-end database.

  4. In the Save as type box, select Access Database (*.accdb).

Top of Page

Periodically move records into an archive table

This is a four step process that requires making an empty copy of the table containing the records that you want to archive, creating an append query to copy the records from the original table to the archive table, creating a delete query to remove the archived records from the original table, and finally creating a macro to run both queries that can be run when you want to archive. This seemingly complex process can be easy if you follow the steps in the order in which they are presented below:

Step 1: Create an archive table

Step 2: Create an append query to copy data to the archive table

Step 3: Create a delete query to remove data from the original table

Step 4: Create a macro to run both append and delete queries

Step 1: Create an archive table

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

To delete your old archive table when you create a new one, instead of doing this step, you can use a make-table query to copy the data to your archive table. To do this, skip to Step 2.

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

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

  2. 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

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

Step 2: Create an append query to copy data to the archive table

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

  2. Use the Show Table dialog box to add the table with the records that you want to archive, and 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 the field and it 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, 2019 by using the expression <#1/1/2019# 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 Introduction to queries.

    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, use 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, use a make-table query to create the archive table by using the specified records:

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

    5. 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 to remove data from the original table

  1. On the Create tab, in the Queries 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.

  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 the append or make-table query used a parameter, ensure that your delete query does so too. Also remember 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 Introduction to queries.

  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 to run both append and delete queries

  1. On the Create tab, in the Macros & Code group, click Macro.

  2. Click the drop down arrow next to Add New Action, and then click OpenQuery.

    The OpenQuery action appears and displays its arguments.

  3. In the Query Name box, select the (append or make-table) query that you created in Step 2.

  4. Click the drop down arrow next to Add New Action, and then click OpenQuery.

    The OpenQuery action appears and displays its arguments.

  5. In the Query Name box, select the delete query you created in Step 3.

  6. Press CTRL+S to save the macro.

    When you want to archive records, run the macro.

Top of Page

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×