Combining Active and Backup Database Records in Union Query

Introduction.

Microsoft Access database can be a maximum of 2GB in size. We can back up old Records (like previous year transactions) into a Backup database safely for later use if the need arises. After the safe backup of these transactions, they can be deleted from the active database and Run Compact & Repair Utility to optimize the database performance in day-to-day activities.

There are times that we need the old data for analysis purposes, like next year's budgeting, sales target setting, and so on. At this point we will need old records to combine with the existing data of the active master-table, in the current database, to do various analysis runs.

The Union Query Solution.

We can merge records of both the active table and backup table (with the same name) in a Union Query.

A simple example is given below:

SELECT Export.* 
FROM Export
UNION ALL SELECT Export.*
FROM Export IN 'G:\NEW FOLDER\DB1.MDB';

From the above example, you can see that the name of the table Export in an active database and backup database(DB1.MDB) are the same. No need to link the table to the active database to access the data from the backup database.

• Union Query
• Cross-Tab Union Queries for Charts
• ChDir and IN Clause of Access Query
• Deleting Folders with DOS Command