Archive Access data

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.

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

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.

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

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.

   

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. Add the table with the records that you want to archive.

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.

   

   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:

   

   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.

      

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

      

      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. Add the table with the records that you want to archive.

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.

   

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