Archiving Master Data ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Over a period of time, the Master Table of our database can grow into large volumes, having thousands or millions of records. The maximum logical size of a Ms-Access Database is only 2GB. Everyday Repairing and Compacting may not improve the performance of the database. The processing time of reports or running time of Queries will keep on increasing.

The majority of old records in the main table may not involve in daily or monthly querying or report preparation processes. But, old records may involve in year-end processes, like history analysis, next year's business target setting, and so on.

Normally, old records of the master tables are set with a flag indicating as archived and retained in the master table itself. Active records are filtered from the master table for reports or querying to monitor the current activities of business functions. The filtering and sorting process may take more time, every time, due to the large volume of data in the main table.

Maintaining Data of Earlier Period.

Removing the old data from the main table and keeping them in a separate database will improve the active database’s performance. The older data is in the archive.accdb (a name for reference) can be easily made available to the Front-End database for year-end processes.

You don't need to link and keep the archived table in the Front-End database permanently. You can combine the archived data with the active master table in a Union Query, by using direct references to the archived table, rather than keeping it linked to the FE database. The Union Query having combined data of both the tables can be used as a source for year-end processes. You can find more details on Union Queries here.

But first, let us see, how we can safely transfer the old data from the master table (tblMaster) to a new archive database: Archive. Here, we assume that we have a Front-End, Back-End configuration of databases.

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 connected to a common Network Server then create a folder there/use an existing folder, where you have access rights, and save the archive.accdb there. The database will be backed-up regularly, by the Network Administrators and will be safe for recovery if the need arises. Click on the link to learn more about Database Daily Backup.
Close archive.accdb database.
Open the Back-End Database.
Transfer only the Structure of tblMaster into the Achive.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 table tblMaster.
Close BE database and open the Archive database.
Open tblMaster in the archive database and check the count of records that matches with the count taken earlier. If not, investigate the cause and redo from step 5 onwards, after deleting the wrong data in 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 with the count you have taken earlier.
Run the Delete Query to remove the records from the tblMaster table from the BE database.
Run Repair and Compact option to reduce the database size.
Close 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.

Step-1 to step-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 Definition Only option to transfer the tblMaster Table Structure into Archive.

Step-5: The 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 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 Append Query in Datasheet View and check the record count with the count you have taken 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 tblMaster table and to append them in the archive.accdb tblMaster table.

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