Archiving Data - Best Method?

[Sonny Jim]

1. Will archiving older data improve response time of a database?
2. What is the best way to archive data knowing that older records will still be accessed twice a year?

 

[pbaldy]

1) Only if there are a massive number of records (think millions, not thousands).
2) Most of us would just have a field in a single table to designate archived. Then you can either get current data, archived data or both just by using the appropriate criteria.

 

[apr pillai]

If you have large amount of old data then you may try the following method to reduce the current database size and enhance its performance:

1. Export the old data into a Table, with the same name, in an external Access Database.
2. Delete the exported entries from the current database Table.
3. Create a Union Query, example SQL is given below for reference:
   
   

   SELECT MASTERTABLE.* FROM MASTERTABLE
   UNION ALL SELECT MASTERTABLE.* FROM MASTERTABLE IN 'F:\mdbs\BACKUP.MDB';

   
   
4. Save the Query with an appropriate name.
5. Use this Query as Source for periodical Reports or Views, where archived data involves.
6. Run Compact & Repair Database option to reduce the size of current database.
7. Future archiving action can be done through an Append Query with the IN CLAUSE to add the data directly to the existing MASTERTABLE in the external Backup.mdb database.