Archiving an Access Database
Databases naturally become slower as the size of the database increases. Therefore to maintain the speed of very large databases, it is recommended to periodically archive older data from the current database to an archive database (see Optimizing the Database for Speed). Once the data has been archived it is no longer available within the current database, but can be accessed by closing the current database and opening the archive database. All unique record numbers that were archived and purged from the current database become available for use again in the current database (e.g. repair order numbers, inactive customer accounts; see also Business Location - Number Ranges Tab). By default, data older than three years will be archived.
NOTE Before archiving a database, you must ensure that all other users are out of the database. This includes temporarily closing the At Your Service – Internet Utility (see Exiting the Internet Utility).
To archive data, first close all windows within the main
program shell and then select File 
Archive Old Data from the main menu.
Only the program administrator (see User
Security) can archive data. Enter archive options and click the
Archive button to begin archiving. A prompt will verify the request to
archive the database before proceeding, and a backup copy of the original
unarchived database will automatically be made in case there is a need to go
back. The backup copy is named the same as the original database but with
“-Backup” appended to the file name, and it is saved in the same folder
as the original database. Click the Close button or press the Esc
key to quit without archiving.
Each archive option is discussed in the following table.
Archive Old Data Entry Fields
|
Field |
Description |
|
Archive Records Before |
Enter or select a date before which records will be archived. The default is January 1 of three years earlier than the current date (e.g. if the cutoff date March 5, 2005 then the archive date will be January 1, 2002). The date may not be in the future. |
|
Archive Database Suffix |
Enter a suffix name to be appended to the end of the database name being archived. The default is the four-digit year of three years earlier than the current date. |
|
Keep Repair Records that are Still Under Warranty |
Check this box (the system default) if closed repair orders that are still under warranty should be excluded from the archive process. Uncheck this box if all closed repair orders from before the archive cutoff date should be removed from the current database and moved to the archive database. If items on archived repair orders are later returned for repair under warranty then the archive database would need to be opened to look up the repair history on those items. |
|
Purge Inactive Accounts from Current Database |
Check this box (the system default) if inactive customer and vendor accounts should be purged from the current database if all records that reference those accounts have also been archived and purged from the current database. Uncheck this box if no customer or vendor accounts should be purged from the current database. |
The following rules or conditions are used when selecting which records are to be removed from the current database. All records and data not meeting these rules or conditions will be retained in the current database.
Archiving Conditions for Removing Data from the Current Database
|
Module |
Conditions for Archiving Data |
|
Customers |
If the Purge Inactive Accounts from Current Database option is checked then inactive customer accounts will be archived if they are no longer referenced by other records in the current database. |
|
Vendors |
If the Purge Inactive Accounts from Current Database option is checked then inactive vendor accounts will be archived if they are no longer referenced by other records in the current database. |
|
Repair Orders |
Repair invoices (i.e. repair orders with a repair invoice status; see Repair Invoice Statuses) that have an invoice date before the archive cutoff date and have been fully paid will be archived. If the Keep Repair Records that are Still Under Warranty option is checked then the repair invoices must also no longer be within the repair warranty period to be archived. |
|
Credit Notes |
Credit notes that have a credit note date before the archive cutoff date and have been fully applied will be archived. |
|
Sales Invoices |
Sales invoices that have an invoice date before the archive cutoff date and have been fully paid will be archived. |
|
Purchase Orders |
Purchase orders that have a purchase order date before the archive cutoff date and have been fully received will be archived. |
|
Purchase Returns |
Purchase returns that have a purchase return date before the archive cutoff date will be archived. |
|
Internal Ledgers |
All transactions in internal ledgers that have a transaction date before the archive cutoff date will be archived. |
The following rules or conditions are used when selecting which data is not moved to the archive database. All other records and data from the current database not meeting these rules and conditions will still be retained in the archive database.
Archiving Conditions for Not Moving Data to the Archive Database
|
Module |
Conditions for Archiving Data |
|
Repair Orders |
All open repair orders (i.e. repair orders with a repair order or repair estimate repair status; see Repair Order and Repair Estimate Statuses) will not be moved to the archive database. Repair invoices (i.e. repair orders with a repair invoice status) that have an invoice date on or after the archive cutoff date or have not been fully paid will not be moved to the archive database. If the Keep Repair Records that are Still Under Warranty option is checked then any completed repair invoices that are still within the repair warranty period will not be moved to the archive database. |
|
Sales Invoices |
Sales invoices that have an invoice date on or after the archive cutoff date or have not been fully paid will not be moved to the archive database. |
|
Credit Notes |
Credit notes that have a credit note date on or after the archive cutoff date or have not been fully applied will not be moved to the archive database. |
|
Purchase Orders |
Purchase orders that have a purchase order date on or after the archive cutoff date or have not been fully received will not be moved to the archive database. |
|
Purchase Returns |
Purchase returns that have a purchase return date on or after the archive cutoff date will not be moved to the archive database. |
|
Internal Ledgers |
All transactions in internal ledgers that have a transaction date on or after the archive cutoff date will not be moved to the archive database. |
Database archiving is not available for MySQL or Microsoft SQL Server databases.
Reporting on Archived Data
After archiving, your historical accounting data will be split between two database files. Since the accounting data will be unique in the individual database files, it is possible to open each file in turn and run the same report with the same report parameters. If the totals at the bottom of the two reports are manually added together, then they will be the same as if the report had been run on the complete database file before archiving. This same principle can be applied if the database is archived multiple times into multiple database files.
Working With MySQL Databases