Microsoft Access is a very powerful database that works with great speed and efficiency helped by the fact that there is one main MDB file that stores all the data required by Access. If one continues storing data in this file, then the size of the MDB file is sure to affect the performance of the database. Further, there is an increased risk of the MDB file getting corrupted if it becomes too large. In fact, in such cases, where the corruption in MDB files is very high, only third-party tools such as Advanced Access Repair from DataNumen prove to be effective in restoring all the data. In order to avoid these problems, it is recommended that rather than storing all your data in the MDB file, users should regularly archive Access data so that the main MDB file deals with just the active records. This makes it easier to take backups and conduct an mdb recovery as well.
The best practice would be to periodically archive old or inactive records - this reduces the load on the main MDB file and will also help you stay organized. For example, suppose you run a store and have detailed records of all sales. You might have a table that tracks the product sold, date on which it was sold along with the date beyond which it cannot be returned. You can then set an archiving policy that will archive all the sales records that are a year old and delete them after a couple of years. You can archive the records using any kind of filter that you want - just sales of certain products retaining records of major sales in the main MDB file or all the sales of one year. You can then focus on ensuring the security of the current records so that during an mdb recovery, there is no loss of current data.
As we explained with the example above, you very often have to deal with data that is no longer required but would like to keep handy just in case you ever do need it. By archiving this data, you are ensuring that the data is available when you need it without cluttering up the present data. It is best to give some thought to how you would like to organize the data and then come up with a data archiving policy. Here is an example of how you might want to archive the data.
You might want to archive data based on the date of the records. For example, you might want to select all the sales made in one year and store them in an archive table. To do this, a query is run on a periodic basis to select the data that meets your condition, which is then appended to the archive table. Another similar query will be used not just to select the data but also delete it from the main MDB table. When you wish to carry out an mdb recovery to find a record from a particular year, you can always run a query on the archive table and retrieve the record.
The best practice would be to periodically archive old or inactive records - this reduces the load on the main MDB file and will also help you stay organized. For example, suppose you run a store and have detailed records of all sales. You might have a table that tracks the product sold, date on which it was sold along with the date beyond which it cannot be returned. You can then set an archiving policy that will archive all the sales records that are a year old and delete them after a couple of years. You can archive the records using any kind of filter that you want - just sales of certain products retaining records of major sales in the main MDB file or all the sales of one year. You can then focus on ensuring the security of the current records so that during an mdb recovery, there is no loss of current data.
As we explained with the example above, you very often have to deal with data that is no longer required but would like to keep handy just in case you ever do need it. By archiving this data, you are ensuring that the data is available when you need it without cluttering up the present data. It is best to give some thought to how you would like to organize the data and then come up with a data archiving policy. Here is an example of how you might want to archive the data.
You might want to archive data based on the date of the records. For example, you might want to select all the sales made in one year and store them in an archive table. To do this, a query is run on a periodic basis to select the data that meets your condition, which is then appended to the archive table. Another similar query will be used not just to select the data but also delete it from the main MDB table. When you wish to carry out an mdb recovery to find a record from a particular year, you can always run a query on the archive table and retrieve the record.