Using Queries to Archive Data

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:

  • If the current table uses an AutoNumber data type to assign a unique value to each record, change that to a Number data type in the historical table.
  • You might not need to retain the primary key. Whether you do depends on the existing relationships. Don't delete the primary key if you need to rely on those relationships when searching or retrieving data. If you decide to delete the primary key, be sure to index that field to improve retrieval and search performance.

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:

  • Discuss archiving while you're designing the database. Don't wait until the application is up and running-that's definitely the wrong time to add an archiving feature.
  • If possible, flag records instead of copying them to another table.
  • When deciding where to store your archived records and whether to keep a live link to those tables, you must consider your network resources-assuming, of course, that your system is networked.

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 :

  • Verify that the correct table has been selected as the target table by clicking the Query Type button on the Query Design toolbar. If the target table has been renamed, you'll need to reselect it.
  • Make sure that all (and only) the fields with data to be appended are selected in the design grid.
  • Verify that the appropriate field in the target table is selected in the Append To cell for each query column. (Reselect the field to ensure that it exists in the target table.)
  • For each query column, make sure that the source field and the target field have matching data types.



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net