WELCOME TO GMS ACCOUNTING ONLINE HELP
The help you need at your fingertips.
You are here: Tools > Archive Database
Archive Database
After you have been using the Windows Accounting System for several fiscal years, you will notice that it takes longer to find data in the tables when you are processing reports. That is because your tables have so many records that it needs to search through. You may want to archive your database, which basically means you will save a copy of your entire database and then it will delete records as of the dates you enter. When you archive, it will delete all records from the earliest date through the cutoff date.
Things You Should Know
Monthly Processing
Payroll and 1099 Information
Inactive Master Files
Inactive Gl/Elements
Inactive Vendors
Terminated Employees
Inactive Customers
Disposed Fixed Assets
Completed Purchase Orders
Other Information
Operating Instructions
The software will automatically make a backup of your present database (Access databases only) and give it the name “(name of your database)arch(date).mdb”. For example, it may be called conversionnetarch9-15-19.mdb.
For clients using a SQL database, GMS recommends you make a copy of your database before proceeding.
Click on Tools, Archive Database.
Note: We recommend archiving one year at a time, starting with the oldest year, for best results.
Select Current Database: Click on the button next to the text box to designate where your current database is located.
Monthly Processing Cutoff Date: Select a fiscal year ending cutoff date that you wish to delete records from your database.
Payroll & 1099 Information: Select a calendar year ending cutoff date that you wish to delete records from your database.
Archive Inactive Master Files: Put a checkmark in any of the check boxes that you wish to delete inactive, terminated, disposed or completed items.
Archive: Click on the archive button once all of the desired selections and dates have been entered. A copy of your database will be made first as explained in the first paragraph under Operating Instructions. After the archive process has been completed, a message will appear indicating that the process is complete. You will be requested to compact and repair the database. Microsoft Access users should run the compact and repair. SQL Server users should request that their IT staff shrink the database.
Note: Should you wish to archive inactive master files only (without archiving monthly processing or payroll and 1099 information) select the date in the appropriate combo box above the master file you wish to archive and click on the archive button.
Archive History: This selection will generate a report providing the date(s) your database has been previously archived and the data that was archived at these times.
Things You Should Know
Monthly Processing
- The archive database routine will not allow you to delete the previous fiscal year under Monthly Processing activities. Only two or more fiscal years before your present fiscal year ends will be available for you to archive. For example, if you started using the Windows Accounting System for fiscal year beginning 7/1/2003 and your current fiscal year ends 6/30/2019, you will have fiscal years ending 6/30/2017 and prior available to delete records from. We recommend archiving one year at a time, starting with the oldest fiscal year, for best results.
- Under Monthly Processing, records will be deleted from the tables used for the General Ledger, Prior Year Budgets and prior year amounts, journal entries, accounts receivable and cash receipt batches, cost allocation, timesheets and expense reimbursements, assuming that posted periods exist in the General Ledger table that are exactly two or more years before the period ending date.
- Only outstanding invoices as of the cutoff date will not be deleted. If there are vouchers in the same batch that are not outstanding, they will be deleted.
- When archiving, the system will check to see if any document numbers for vouchers, checks or A/R Invoices that are being archived exist in the database after the cutoff date (such as voids). If they do, the batch in which the original document is contained will not be archived.
- Throughout this section we refer to "inactive" codes and files. This simply means that the active button for a specific code or file has been unchecked.
Payroll and 1099 Information
- The archive database routine will not allow you to delete records for the 3 previous calendar years under Payroll and 1099 activities. For example, if the current calendar year is 2019 the system will not allow you to remove any Payroll or 1099 activities for January 1, 2016 and forward.
- Under Payroll and 1099 Information, records will be deleted from the tables used for voucher and cash disbursement batches, prior year payroll, retirement, workers comp, and weeks worked.
- Only outstanding vouchers as of the cutoff date will not be deleted. If there are vouchers in the same batch that are not outstanding, they will be deleted.
Inactive Master Files
Inactive Gl/Elements
- If any inactive GL codes are set up in the fixed asset inventory, they will not be deleted.
- If GL Code 50000 is marked as inactive, it will not be deleted.
- Inactive GL codes and elements will use the Monthly Processing cutoff date.
- If any inactive GL codes or elements exist in tables after the cutoff date that affect tables related to the General Ledger, Cost Allocation, budgets/prior year, and class setup they will not be deleted.
- Inactive GL codes and elements that are deleted from the master files will automatically be deleted from any setup files.
Inactive Vendors
- Inactive vendors will use the Payroll and 1099 Information cutoff date.
- Vouchers coded to vendors after the cutoff date will not be deleted.
- Inactive vendors that have no vouchers associated with them will be deleted from tables related to vendors, vouchers and cash disbursements.
- The software will check the check dates and compare to the cutoff date entered before archiving those vendors.
Terminated Employees
- For terminated employees, we will compare the termination date to the Payroll and 1099 Information cutoff date.
- Terminated employees that exist in the current YTD payroll table will not be deleted.
- Employees that were terminated on or before the cutoff date will be deleted from all tables related to employee files, timesheets, worker’s comp, retirement, payroll adjustments, prior year state tax analysis, prior year payroll information, weeks worked, personnel history and prior year cost allocation.
Inactive Customers
- Inactive customers will use the Monthly Processing cutoff date.
- Invoices coded to customers after the cutoff date will not be deleted.
- Inactive customers will be deleted from tables related to customers, invoices and cash receipts.
Disposed Fixed Assets
- For disposed fixed assets, we will compare the disposition date to the Monthly Processing cutoff date.
- Disposed fixed assets will be deleted from tables related to the inventory, location history, depreciation history and funding sources.
Completed Purchase Orders
- Completed purchase orders will use the Payroll and 1099 Information cutoff date.
- A completed purchase order that has been used in a voucher after the cutoff date will not be deleted.
- Completed purchase orders will be deleted from all tables related to purchase orders if the PO date is on or before the cutoff date.
Other Information
- The dates used to compare to the cutoff date will generally be posting periods except all check information will use check dates.
Operating Instructions
The software will automatically make a backup of your present database (Access databases only) and give it the name “(name of your database)arch(date).mdb”. For example, it may be called conversionnetarch9-15-19.mdb.
For clients using a SQL database, GMS recommends you make a copy of your database before proceeding.
Click on Tools, Archive Database.
Note: We recommend archiving one year at a time, starting with the oldest year, for best results.
Select Current Database: Click on the button next to the text box to designate where your current database is located.
Monthly Processing Cutoff Date: Select a fiscal year ending cutoff date that you wish to delete records from your database.
Payroll & 1099 Information: Select a calendar year ending cutoff date that you wish to delete records from your database.
Archive Inactive Master Files: Put a checkmark in any of the check boxes that you wish to delete inactive, terminated, disposed or completed items.
Archive: Click on the archive button once all of the desired selections and dates have been entered. A copy of your database will be made first as explained in the first paragraph under Operating Instructions. After the archive process has been completed, a message will appear indicating that the process is complete. You will be requested to compact and repair the database. Microsoft Access users should run the compact and repair. SQL Server users should request that their IT staff shrink the database.
Note: Should you wish to archive inactive master files only (without archiving monthly processing or payroll and 1099 information) select the date in the appropriate combo box above the master file you wish to archive and click on the archive button.
Archive History: This selection will generate a report providing the date(s) your database has been previously archived and the data that was archived at these times.