Combining Active and Backup Database Records in a Union Query.
Microsoft Access databases have a maximum size of 2 GB. The Database's operational efficiency degrades when the contents grow over time within the 2 GB limit. The business transactions that are 2 years old or older are copied into a separate backup Database for safekeeping and future reference. After verifying and confirming that the copied records are accurate in both databases, delete the corresponding records from the active database.
Finally, run the Compact & Repair utility to optimize the database for day-to-day operations.
However, historical data is required for analysis purposes—for example, to prepare budget estimates, sales targets, or analyse trends. In such cases, we may need to reintegrate archived records with the current master table in the active database to perform meaningful analysis.
The Union Query Solution.
We can merge records from 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 Exported.* FROM Export IN 'G:\NEW FOLDER\DB1.MDB';
In the example above, you can see that the Table names Export in the active and the backup Database (DB1.MDB) are the same. No need to keep the table linked to the active database to get data from the backup database. The Export Table Records in DB1.MDB are referenced in the Query and records combined with the Export Table in the active Database.
