Creative Financial Software

Data File Archiving

What is Data File Archiving? Data File Archiving in QuickBooks is the removal of selected transactions, usually based on the financial period in which the transaction is dated. For example, if your QuickBooks file contains transactions dated from January 2002 through the present, you may choose to archive (i.e., remove) just the data dated 2002-2007.

What is the Benefit of Data File Archiving? Archiving (removing) data from your QuickBooks data file will significantly reduce the size of your data file and will probably increase QuickBooks performance, especially when running reports and when using QuickBooks in a multi-user environment. Smaller files are also easier to manage, especially when you need to send copies of your data to QuickBooks consultants and/or tax preparers. The most compelling benefit in the increase in QuickBooks performance.

How Do I Know if I Should Archive (Remove) Data from My QuickBooks File? Depending on the type of information you enter into QuickBooks and the number of transactions you process each day, your QuickBooks data file could grow rapidly. In some cases we have noted growth in our clients' data files at the rate of 100MB a year (or even faster!). QuickBooks versions 2006 and later operate on a robust SQL database designed by Cybase. (Click here for more information about the QuickBooks' SQL database.) This new database is much more robust than that used by QuickBooks 2005 and prior. However, if you use QuickBooks Pro or Premier 2006 or later, you may begin to experience significant decreases in performance after your file exceeds 100MB. If you use QuickBooks Enterprise Solutions, you may begin experiencing significant performance problems after your file exceeds 400 to 500MB.

How Does Reducing the Size of My QuickBooks File Increase QuickBooks Performance? At Creative Financial Software we refer to QuickBooks as a "perpetually posting" accounting solution. By that, we mean that QuickBooks generates financial reports by referring to each transaction in the file. Each individual transaction therefore dynamically impacts QuickBooks reports. For example, if you create a Balance Sheet in QuickBooks, the program calculates the balance in your checking account by referring to each check, deposit, or transfer that makes the balance in the checking account go up or down. QuickBooks calculates the balance as of the date of the Balance Sheet report by referring to hundreds (if not thousands) of individual transactions. If you change even one of these transactions at any point in the past, QuickBooks will calculate a different balance on the Balance Sheet. Each check, deposit or other banking transactions therefore perpetually affects QuickBooks reports. Because of this perpetually posting nature, you can see how QuickBooks performance could decrease as you enter more and more transactions into your file.

Many accounting programs allow you to clear the individual transactions from the database at the end of each fiscal year. This process in other accounting systems is called "closing" the period. Some programs flush this individual transaction detail monthly as well. When you "close" the previous fiscal year in QuickBooks, the program will lock down the previous years' data so you cannot edit, add, void or delete any transactions in the the closed periods, but QuickBooks still retains the individual transactions and refers to them each time you run reports.

What Are My Data File Archiving Options in QuickBooks?

Within the program you have two utilities that address excessive data file growth. However, both options have negative as well as positive virtues.

Condensing. This utility is called "Cleaning Up QuickBooks data through a specified date" in the most recent versions of QuickBooks, but the process is the same. When condensing, QuickBooks removes many of the transactions dated on or before the date you condense/clean up the data in an attempt to decrease the size of the file and to increase the performance of the file. What is the downside? This utility is not very effective if you use inventory items on purchase forms and/or sales forms. Other data conditions can impact the effectiveness of the tool as well. Also, in our experience, 1 out of 10 condensed data files experience some type of data file corruption. Common corruptions include excess data in the Open Invoices and Unpaid Bills Detail reports that are impossible to remove and that cause these reports to no longer agree to the Balance Sheet. In some cases, the Balance Sheet no longer balances (Assets do not equal Liabilities + Equity).
Removing All Transactions from the File. This utility is an alternate selection when you "Clean up Company Data," but this utility produces a very different result - removing all transactions from the file. This is a powerful utility that we often use a Creative Financial Software to radically reduce data file size or to give clients a fresh start when their historical financial information is grossly incorrect. What is the downside? This tool has one glaring weakness - you can't retain any transactions. Only list information, user settings, memorized transactions, memorized reports, form templates, budgets and timesheets remain in the data file. Since you cannot choose to retain the most recent 1 to 2 years of data, you do not benefit from comparative reporting until you have used your purged data file for a several months (or in some cases more than a year). Of course you can save a copy of your data file before purging the transactions and you can use the information in the pre-purge copy for comparative reporting. However, doing so is much more tedious and ineffective because you can only compare information by printing reports from two separate QuickBooks data files or by exporting reports to Excel from two separate QuickBooks data files. There is another very practical and negative impact as well. You cannot retain open invoices, unpaid bills, open Sales Orders, open Estimates or open Purchase Orders - transactions you need to manage your relationships with your customers and vendors.

How is the Data File Archiving Service You Offer Better?

At Creative Financial Software we recognize the need to remove data from the data file to decrease the size of the file and to increase QuickBooks performance. However, we avoid the issues created by the QuickBooks utilities we noted above.

Our archiving procedure never causes data file corruption issues.
Our archiving is consistent. For example, we purge selected transaction types from your file regardless of whether or not the transactions include inventory items.
Unlike the "Remove All Transactions"' utility, we can remove transactions for a specific date range. For example, we can archive (remove) transactions dated 2007 and prior only. Any transactions dated 2008 or later would remain in your file. You keep the most recent 1 to 2 years of data for comparative reporting and for historical reference when speaking with customers and vendors. You also retain all Open transactions that you need to manage your customer and vendor relationships.

How Do You Perform the Data Archive? Our Data File Archiving is a very involved process that requires the use of a utility developed by a third party called Data File Transfer Utility (www.q2q.us) along with a stringent set of procedures we use to preserve the integrity of your financial information in spite of the removal and subsequent transfer of hundreds or thousands of QuickBooks transactions. In effect, we do "surgery" on your QuickBooks data file to archive selected financial years only - and we do so with 100% success. The developer of the Data File Transfer Utility has such confidence in our archiving procedures that when his customers need help using his utility to archive their files, he refers these customers to us - and only to us - out of all QuickBooks consultants in the country! Contact us for more information about this critical data file archiving service and to archive your QuickBooks data.

Note: There are several factors that could impact QuickBooks performance. The size of the data file is one of these factors and is perhaps a critical factor. Others include:

The power of your computer hardware - including both your server and each workstation in your network. At times, even your router or the type of cable you use in your local area network can adversely impact QuickBooks performance.
The number of users logged into QuickBooks simultaneously.
The type of work you are doing in QuickBooks at any given time. Certain features in QuickBooks demand higher system resources than others. For example, if you merge two accounts together or if you change the account to which a sales item is linked (and you make the change retroactive), QuickBooks uses tremendous resources to process these types of changes. Also, entering transactions requires less resources than editing, voiding or deleting transactions, because edits, voids and deletions create more detailed entries on the Audit Trial report, Voided/Deleted Transactions report, and (if applicable) the Closing Date Exception report.
Other programs running on the workstations and/or the server. If you use your server to host your QuickBooks data file and for other purposes - like running Exchange Server to network your Outlook file or to run other networked applications - QuickBooks performance will decrease.

There are other factors that could impact QuickBooks performance, but the ones mentioned above have the most significant impact. If you are experiencing poor QuickBooks performance, we recommend addressing each of the issues in the bullets above and making changes wherever possible. If you still experience poor QuickBooks performance after making necessary changes, your best option may be to reduce the size of your data file.