06 Aug QuickBooks Data File Health
The time of the year is going to be quickly upon us when Intuit traditionally releases a new version of the software, and clients begin asking questions not only about upgrading but also about new files. While we take the approach that each situation is unique and there is not one size that fits all situations, there are a few data health issues we have been seeing more frequently that bear mention.
Assess the Health of the File
In my opinion, the first step is to take a step back and really look at the health of the file. Some people take a Balance Sheet approach as the first step: Go through each account and make sure that it looks accurate, proper procedures are being used, detail reports look reasonable, etc. Others have a checklist that they use in Excel or Word as part of their procedures. Others use our Small Business Diagnostic Tool: QuickBooks Edition (http://www.accountingsoftwaresecrets.com/eliminate-one-your-biggest-quickbooks-headaches) to provide a consistent approach. No matter what works the best for you, regular analysis is important to discover issues, hopefully before they become so prevalent that you have a big clean up job on your hands.
One issue that is important to note is that QuickBooks has been the small business accounting software of choice for many years, and because of this, clients have often been using the same data file for that entire time. All of the version upgrades (through feature enhancements and data base changes) can take their toll on the file. We have seen instances where a single file has been upgraded from version to version and release to release in excess of 35 times. That is a lot of data base manipulation in addition to the strain of all the transactional data entry. I cannot say that this is why we are seeing more data integrity issues, but it seems like a logical contributing factor.
To determine the file and size, number of data base file fragments, and number of versions used on the file, press F2 (or Ctrl+1) and in the product information screen you will see all of this and more:
Data Integrity Overview
One of the best ways to quickly and easily determine if there is an underlying database issue is the process of verifying data
integrity. This is a critical component of QuickBooks data file maintenance that should not wait for a formal assessment of the file. This is something that should be done on a regular basis. Our rule of thumb is immediately anytime there is an issue, such as getting kicked out of QuickBooks, but at least monthly. For our clients, we encourage them to add it to their month end procedures and complete the process when working on the bank reconciliation. Others get in the habit of doing it last thing each Friday for the consistency. We have been seeing much more file damage recently and the sooner you catch the issue, the more likely it is that the rebuild functionality will resolve the issue before you end up needing Intuit’s Data Services team.
How to verify the data
It is recommended that the verify data be done in single user mode for the most complete analysis. To start the analysis, choose File > Utilities > Verify Data. There will be a progress bar to show that the process is working.
It is not uncommon for the top of the progress bar box to state that the software is not responding, especially for larger files. Do not be quick to cancel or restart the computer. This seems to be a very common occurrence and if you just wait, it will probably be ok. If you are unsure if it is working or not, open the Windows Task Manager and on the process tab find the QuickBooks program file (mine is QBW32.exe) and confirm the memory usage is changing.
It is also important to note that just because the data integrity returns that there were no problems detected with your data that does not mean you do not have data base damage. It simply means, for the tests that it completed, it did not find any issues. I equate it to going to the doctor for your annual physical, you have tests, you have an exam, you even have a discussion with your doctor to discuss your situation, but that does not mean that you don’t have any health issues that were not discovered.
Data Integrity Issues – Now What?
Depending on the issue that the verify encounters, there will be a box that states that the data has lost integrity and the next step is to do a rebuild, or it might provide an explanation of the issue with a link to the support documentation. For the later, follow the link to discover the recommended steps to correct the issue. For the former, there are several things you may need to do.
The first suggestion is to rebuild the data file. To perform this process, choose File > Utilities > Rebuild Data (the option below Verify Data). This will prompt a new back up of the data file, and then the software will try to correct the issue. Once the Rebuild is complete, verify the data again to confirm that the issue has been resolved.
If the Rebuild does not fix the problem, it is possible to review the log file by pressing Ctrl+1 then Ctrl+2 to access the Tech Help screen. From there, open the QBWIN.LOG and scroll to the bottom to see if the specific transaction(s) are displayed with the explanation of the problem. Note that it is most effective to perform this process immediately after the verify data so the information is at the bottom of the log.
If the specific transactions are listed, go to those transactions, make sure that all the details are noted, and then delete the transaction. Rebuild the file again without the transaction, confirm that there are no problems detected with the data, and then re-enter the damaged transactions. Note that if you just delete and re-enter, the transaction will often still remain damaged. If the file is still not passing the data integrity check, it may be necessary to make a portable company file, open the portable company file and rebuild the file several times. If the file still does not display that there are no issues with the data integrity, it may be necessary to contact Intuit Technical Support to open a case and see if they can provide assistance and/or escalate the case to data services.
When the Rebuild Does Not Work
If the data services team becomes involved, that typically means submitted the file to them so they can try to fix the file damage. Turnaround time will vary, but it is typically 3-5 business days. During this time, any transactional information that is entered into the file should be noted as it will need to be re-entered into the new file. For this reason, it is important to keep the use of the file to a minimum. In addition, keep in mind that there are tools to transfer transactions from one file to another if you really cannot be down for the time that the file is being fixed. However, only new transactions can be transferred using the tools available, not changes, so refrain from any edits to list and transactional data.
When Data Services Cannot Fix the Issue
As a last resort, when nothing else has been successful, there are only two choices left:
- Go back to a prior undamaged back up of the data file and work forward from there. The issue with this, as was mentioned above, is that only new transactions can be imported, not edits. In addition there are numerous limitations with the SDK (software development kit – the tool Intuit has provided to developers to access the QuickBooks data file from a programmatic perspective) one example is the inability to link purchase orders and sales orders to bills and invoices, respectively.
- Create a new file. Note that this is creating a brand new file, from scratch and the go through the data conversion process from there. Simply removing all transactions from the existing file to preserve the users, memorized transactions, etc. has a great likelihood of retaining the file damage as well. Starting with a new file, then transferring in the data will break the links to the old file and insert as new, typically leaving the file damage behind. This can be a time consuming process depending on the amount of data to be transferred. There are also many other issues to be considered such as integrated applications, payroll processing, etc.
Summary
Even the best prevention and maintenance may still result in a damaged data file. However, finding the problem early will hopefully mitigate the impact on the business. We spend a good bit of our time working with the QuickBooks data in one form or another. If you have a specific situation you would like to discuss, we would be happy to see if there is a tool we can recommend or a service we can provide to streamline the process of getting the file healthy and the accounting personnel back to work.