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

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

Normally, old records of master table 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 large volume of data in the main table.

Removing the old data from the main table and keeping them in a separate database will improve the active database’s performance.  The old data in 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 to 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 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.accdb. Here, we assume that we have a Front-End, Back-End configuration of databases.

We need the following steps to complete the process:

  1. Create a new Access Database: archive.accdb 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 archive.accdb there. The database will be backed-up regularly by network administrators and will be safe for recovery, if need arises. Click on the link to learn more about Database Daily Backup.

  2. Close archive.accdb database.
  3. Open the Back-End Database.
  4. Transfer only the Structure of tblMaster into archive.accdb
  5. Create a SELECT Query on tblMaster with appropriate criterion to select the old data.
  6. Open the Select Query in Datasheet View, take the total record count and note it down.
  7. Change the SELECT query into an Append Query.
  8. Save and run Append Query to transfer the selected data directly to the archive.accdb database into table tblMaster.
  9. Close BE database and open archive.accdb database.
  10. Open tblMaster in archive database and check the count of records 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 archive.accdb.
  11. Close the archive.accdb and open BE database.
  12. Create a Delete Query using tblMaster with the same Criteria you have used in the Append Query.
  13. 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.
  14. Run the Delete Query to remove the records from tblMaster table from BE database.
  15. Run Repair and Compact option to reduce the database size.
  16. Close BE database and open FE database.
  17. Create a Union Query to combine data from tblMaster in BE and from tblMaster in Archive.accdb.

Step-1 to step-3 are self explanatory.

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

  • High-light Export option and select Access Database from the displayed menu.
  • Click on 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.accdb.

Step-5: sample 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 earlier period.

Step-6 is self explanatory.

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

  • Click on the Append Query button on the Tool bar.
  • Select tblMaster from the Table Name drop-down control on 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. 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 count matches 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.accdb and delete them from the BE database.  No other change required anywhere.