Combining Active and Backup Database Records in Union Query
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 need arises. After the safe backup 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 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.
We can merge records of both active table and records of 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 active database and backup database(DB1.MDB) are same. No need to link the table to the active database to access the data from the backup database.