If the "child" records cannot always be safely deleted, consider archiving all the tables in the database.Ĭreate a query that selects "parent" records that don't have "child" records. This ensures that all related "child" records will be deleted. ![]() If the "child" records can always be safely deleted, make sure that the relationship enforces referential integrity, with cascading deletes. For example, to archive records of assets that are lent at a library, first determine whether there are any transactions open on those assets, i.e., whether the assets have been checked out but not returned. To account for referential integrity, take the following steps:ĭetermine what "child" records belong to the records that you want to archive. This could cause problems with data integrity and functionality in your database that uses the "orphan" records. This could cause problems if you have already appended the "parent" records to the archive table.īecome "orphans" - records that belong to a "parent" that does not exist. Prevent you from deleting the "parent" records. If the records that you want to archive are "parent" records (they belong to the "one" side of a one-to-many relationship), when you archive them their related "child" records may: If the records that you want to archive are "child" records (they belong to the "many" side of a one-to-many relationship), you can probably safely archive them without concern. If the records that you want to archive are related to records in other tables, you may have to work around the relationship. Warning: If you need to use parameters in your queries, you should create a form to handle the parameters. See the section on Working around referential integrity for more information. May have to work around referential integrity, especially if the records that you want to archive are on the one-side of a one-to-many relationship. Some of the records in a table meet the condition that you use to determine whether to archive.ĮXAMPLE: You want to archive library transactions (check outs) if the Checked In Date is at least a year old. Periodically runs a query that selects the records for archiving, and adds the data to an archive table, and you then run a query to selects the same records (from the original table) and delete them. If you have any lookup tables (tables that you use to store values for looking up, such postal codes or departments), you may need to import that data into the new back-end database. Each year, you archive all of the tables. Requires a split database (a database comprising: a back-end database file that contains all the tables and a front-end database file that contains all the other database objects).Īll the records in most of the tables in a database meet the condition you use to determine whether to archive.ĮXAMPLE: Your database consists of several tables with different kinds of meteorological data. Periodically make an archive copy of the back-end database, and then replace the back-end database with a new, empty one. ![]() May have to work around referential integrity. Every year, you archive the table and start fresh with an empty one. Periodically makes an archive copy of a particular table and replaces that table with a new, empty table copy.Īll the records in a table meet the conditions that you use to determine whether to archive.ĮXAMPLE: You have a table that stores daily temperature extremes. ![]() ![]() The following table lists methods to archive data, a description of the method, explanation of when you should use each method, and other specific considerations for the method. Archiving is also often used to keep data based on a date condition, such as at the end of a month. When a database on your computer contains data that you do not plan to use anymore, but you want to keep that data handy in case you do need it at some point in time, or to meet a data retention policy, archiving is a good way to retain such data. Periodically move records into an archive table This topic explains how to use three different options to archive the data in your Access database. You can archive all the tables in a database, specific tables, or only specific records - for example, records that are older than a particular date. You can keep your Microsoft Access databases organized and manageable by periodically archiving old or inactive records.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |