Archiving Master Data ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Over time, the Master Table in a database can grow substantially, containing thousands—or even millions—of records. Since the maximum logical size of a Microsoft Access database is only 2 GB, routine repairing and Compacting may not be enough to maintain optimal performance. As the table grows, the processing time for queries and report generation will continue to increase.

Most of the older records in the master table are not required for daily or monthly reporting and querying. However, they may still be needed for year-end processes, historical analysis, or setting business targets for the upcoming year.

Typically, older records are marked with an archived flag and retained in the master table, while active records are filtered for routine reports and queries to monitor ongoing business activities. As the table size increases, this filtering and sorting process can become slower, impacting overall database performance.

Maintaining Data of the Earlier Period.

Removing old data from the main table and storing it in a separate database will significantly improve the performance of the active database. The archived data, stored in a database such as Archive.accdb, can still be accessed whenever needed for year-end processes or historical analysis.

There is no need to permanently link the archived tables to the front-end database. Instead, you can directly reference the archived tables in a UNION query, combining them with the active master table only when required. This combined dataset can then serve as the source for year-end processing. (More details on creating UNION queries can be found [here].)

Before we get to that, let us first see how to safely transfer old records from the master table (tblMaster) into the archive database (Archive.accdb). For this example, we will assume that the database is configured in a Front-End/Back-End setup.

The Prelude of our Action Plan.

We need the following steps to complete the process:

Create a new Access Database: Archive in location C:\mdbs or in a location of your preference.

Note: If you are working on a shared network server, create (or use an existing) folder where you have the necessary access rights and save the Archive.accdb there. Databases stored on the server are usually included in the administrators’ daily backup routine, ensuring that your archive remains safe and can be recovered if needed. For more details, see: [Database Daily Backup].
Close archive.accdb database.
Open the Back-End Database.
Transfer only the Structure of tblMaster into the Archive.accdb
Create a SELECT Query on tblMaster with an appropriate criterion to select the old data.
Open the Select Query in Datasheet View, take the total record count, and note it down.
Change the SELECT query into an Append Query.
Save and run the Append Query to transfer the selected data directly to the archive.accdb database into the table tblMaster.
Close the BE database and open the Archive database.
Open tblMaster in the archive database and check the count of records that matches the count taken earlier. If not, investigate the cause and redo from step 5 onwards, after deleting the wrong data in the tblMaster of the Archive.
Close the Archive and open the BE database.
Create a Delete Query that uses tblMaster with the same criteria you have used in the Append Query.
Open the Delete Query in Datasheet View and take the count of records, and ensure that it matches the count you have taken earlier.
Run the Delete Query to remove the records from the tblMaster table from the BE database.
Run the Repair and Compact option to reduce the database size.
Close the BE database and open the FE database.

Linking Old Data to Front-End (FE) Database.
Create a Union Query to combine data from tblMaster in BE and from tblMaster in Archive.

Let us execute the above-defined Plan.

Steps 1 to 3 are self-explanatory.

In step 4: Right-click on tblMaster to display a shortcut menu.

Highlight the Export option and select Access Database from the displayed menu.
Click on the Browse button and select the archive.accdb database and click Save to come back to the Export dialog box.
Click OK to open up the Export Options dialog box.
Select the Definition Only option to transfer the tblMaster Table Structure into the Archive.

Step-5: Select Query Code:

SELECT tblMaster.*
FROM tblMaster
WHERE (((tblMaster.mstDate)<Dateserial(1981,1,1)));

The above criteria will select all the records of 1980 and the earlier period.

Step 6 is self-explanatory.

Step 7: Open the Query created in step 5 in the design view.

Click on the Append Query button on the Toolbar.
Select tblMaster from the Table Name drop-down control in the dialog box.
Select Another Database Radio Button.
Click on Browse… Command Button to find the archive.accdb database, select it and click OK to come back to the dialog box in Query Design View.

Click OK on the dialog box to change the Select Query to an Append Query. The Sample append query SQL is given below for reference.

INSERT INTO tblMaster IN 'C:\mdbs\archive.accdb'
SELECT tblMaster.*
FROM tblMaster
WHERE (((tblMaster.mstDate)<DateSerial(1981,1,1)));

Open the Append Query in Datasheet View and check the record count with the count you took earlier.
If both counts match, then save the Append Query.

Step-8: Right-click on the Append Query and select Open to Run the Query to extract selected data from the tblMaster table and to append it to the archive.accdb tblMaster table.

Click the Yes Command Button on the warning message control to reconfirm the action.

Step-9 to Step-11: Self-explanatory.

Step-12: Sample Delete Query SQL is given below:

DELETE tblMaster.*, tblMaster.mstDate
FROM tblMaster
WHERE (((tblMaster.mstDate)<DateSerial(1981,1,1)));

Step-13 to Step-16: Self-explanatory.

Step-17: Sample Union Query SQL is given below:

SELECT tblMaster.* 
FROM tblMaster
UNION ALL SELECT tblMaster.*
FROM tblMaster in 'C:\mdbs\archive.accdb';

Save the Union Query with the name tblMasterQ. Use tblMasterQ as Source Data for all year-end processes or wherever you need all the data together. For other purposes, your database will run faster.

You can continue to transfer data when they become old, into the Archive, and delete them from the BE database. No other change is required anywhere.