3 4
Archiving data is a hotly debated issue. A developer might feel the need to archive outdated data, but this isn't always necessary. One alternative is to simply flag each record as outdated. The easiest way might be to include a Yes/No field that denotes whether the record is active, and then include criteria in your queries to exclude or include the archived records as needed. Flags are definitely the easiest solution if the amount of data you're flagging doesn't affect performance. Because Access 2002 databases can be up to 2 GB in size, you don't need to be concerned about clearing old records out of the database to accommodate database size limitations, as in years past. (Or, more realistically, if your Access database is approaching 2 GB in size, you should seriously consider upsizing it to a SQL Server back end.)
If you do decide to archive records, you can use the two-query approach: First you run an append query to copy the records to a historical table. Then you run a delete query to remove the records from the active table. Or, if you're copying all the current records, use a make-table query to create the historical table (or an append query to fill it) and then delete all the data from the active table. When dealing with one-to-many relationships, remember to copy and delete related records. As long as the cascading deletes option is on, Access will do the work for you. If that option is off, you'll have to add queries for each related table.
When choosing the two-query approach, you need a historical table for storing the copied records. Usually you can copy the current table by using the method reviewed in the section "Protecting Your Data." If you do, you'll want to make a few changes to the historical table, as follows:
Another issue is whether to keep the historical records in the current database or a linked database. If archived data is rarely needed, you might even consider storing detached records in a separate back-enddatabase. Simply reattach the tables when they're needed. This approach would certainly take time, but the extra time probably won't be a problem if the archived data is rarely needed. If archived records are frequently viewed and you're on a network with multiple servers, you might want to consider keeping your historical data on a separate but attached server. Network speed becomes an issue if you intend to archive data on the same server with your current data, because the overall size of the server will have a definite impact on performance. If you're not networked, you might want to consider storing archive data tables in a separate database, either attached or unattached.
Although a larger discussion of archiving is beyond the scope of this chapter, the following guidelines can help you through the process:
Troubleshooting - My append query doesn't work
If an append query doesn't work or appends the wrong data to the wrong fields, open the query in Design view and do the following :