Auto-switch to archive data table when historical data is required

[neelsfer]

How would i approach this scenario - my main data table (tblAcquire) is getting to big and is draining the system resources, when i do queries.
1.I have a dedicated form with a combobox (frmReports) listing the reports i have available.
2.The data is extracted based on a "StartDate" and "EndDate" textboxes.
3. Is there a way i can tell Access that if data does not appear by date in the "tblAcquire" table, then it must prompt me to temporarily switch the report to "tblAcquireArchive", to access historical data. The "startdate and "Enddate" would be for specific financial periods only, and will not overlap between the current and archived data.

My plan is to display the last date appearing in the archive table, on this form.

I imagine that the row source of the report needs to be automatically changed based on the data i require?

Any suggestions please?

[Seth Schrock]

This might not be the best way to do it, but I would have a table that stores newest date in the archive table. For example if you have archived everything older than 12/31/2012, then I would have that date in this new table. This makes it possible to compare the search dates to the archive date and know which table to pull from. Then in the report's On_Open event, change the record source depending on if your dates are before or after the archive date.

[neelsfer]

thx Seth. I realized now this is quite a job. For the time being, i am going to use an append query and delete query to move old data to an archive table, based on start and end date textboxes. I also added a restore button, to append data back to the main table, when required to do old reports. With time i can start to modify the many reports i have then.

[NeoPa]

Neels. You still need to catch up with Database Normalisation and Table Structures.

You should not be considering archiving the data to separate tables and complicating all of your existing db functionality. Instead you need to think about creating a simple flag that indicates which items should be considered as archived, with all the data sitting in the same table.

If you must archive stuff which is no longer required (and I do in some cases) then do so, but not until it is no longer required in any of your reports.

[twinnyfo]

Neels,

I am with NeoPa on this one. I have code that automatically archives my data, but that data is only saved for historical (and archive) purposes. If I need to access the data, I can, but it cuts my table size (which is accessed constantly) by about 80% right now (that percentage grows over time).

The solution would definitely not be to append and delete your data, as this could quickly produce back end bloating.