17. Managing Your QuickBooks Database

This reference guide would be incomplete without a detailed discussion of the QuickBooks database and managing it. This chapter provides both the business owner and computer professional guidelines for managing the QuickBooks database and any issues that can arise.

The QuickBooks Database

If you are the typical QuickBooks user, you purchased the software to track the income and expenses of your business. By selecting software for this task, you have advanced from a manual recordkeeping system into the world of database technology.

A database is an automated version of a big file room, with information of different types stored in different cabinets, drawers, and files. Years ago, companies employed file clerks to keep track of the company’s paperwork. QuickBooks uses a Database Server to perform similar tasks to a file clerk; organizing and storing the data you create.

Depending on the number of licenses you purchased, you can choose to install the software as a single user application or install it in a network environment for use by multiple users (see Figure 17.1).

Figure 17.1. QuickBooks guides you through the installation of the software.

Based on your installation selection, QuickBooks either installs the personal Database Server or the network Database Server to control the flow of data from the QuickBooks application to your QuickBooks Company file. During the install, the computer operating system creates a Windows user account for this server in order to assign administrative-level access rights to the computer’s resources and the QuickBooks files. When you view your list of users for your computer, included is the QBDataServiceUserxx (where xx defaults to 24 for all QuickBooks 2014 versions). During installation, the QuickBooks Database Server Manager is also installed. This utility is discussed later in this chapter.

System Requirements

Verify that your computer and network hardware meets the minimum requirements for QuickBooks. This list is applicable to QuickBooks 2014 versions, but you might want to check the requirements for your specific version.

Following are supported operating systems for QuickBooks for Windows products:

• Windows 8, either 32-bit or 64-bit versions (note must be on R4 or newer maintenance patch)

• Windows 7, either 32-bit or 64-bit versions

• Windows Vista, Service Pack 1 including either 32-bit or 64-bit version

• Windows XP, Service Pack 3 and later

Computer processor, memory, and disk space requirements are the following:

• Minimum of a 2.0GHz processor; 2.4GHz is recommended.

• Minimum of 1GB of RAM for single-user configurations, 2GB of RAM recommended for multiple users. 2GB RAM required for Windows 8 64bit.

• Minimum of 2GB of RAM for multiuser configurations; 4GB (or more) is recommended.

• Minimum of 2.5GB of disk space for the application.

• 250MB for Microsoft .NET 4.0 Runtime. Additional space is required for your QuickBooks data files.

Additional hardware, software, and Internet connectivity requirements include the following:

• Hardware

• 4x CD-ROM drive required for CD installations

• 1024×768 or higher screen resolution, 16-bit or higher color

• Multiuser—Microsoft Windows XP (SP3), Vista (SP1 with UAC on), Windows 8 (with UAC on), Windows 7 (with UAC on), Windows Server 2003 (SP2), Windows Server 2008, Small Business Server 2008, Windows Small Business Server 2011, Windows Server 2012 (Windows Home Server is NOT supported.)

• Internet connectivity

• Internet access with at least 56Kbps connection speed

• Cable or DSL modem is highly recommended over dial-up service

• Internet Explorer 7, 8, or 9

• Minimum browser requirement—Internet Explorer 7, 8, 9, and 10 with maintenance release 3 or newer as discussed in this chapter. Firefox and Chrome are not currently supported.

• Software for Integration Capabilities

• Microsoft Word and Excel integration requires Word 2003, 2007, 2010, 2013/365 and Excel 2003, 2007, or 2010 (including 64-bit).

• Synchronization with Outlook requires QuickBooks Contact Sync for Outlook 2003, 2007, and 2010 (including 64-bit) (downloadable for free at www.quickbooks.com/contact_sync).

• To learn more about synchronization with Outlook, see “Using Other Planning and Management Tools,” p. xxx.

• Email Estimates, Invoices, and other forms with Windows Outlook, Outlook Express, and Mozilla Thunderbird, as well as web mail services such as Gmail, Yahoo! Mail, and Hotmail.

• Compatible with QuickBooks Point of Sale version 10.0

• Import data from Excel and other programs

• Transfer data from Peachtree 2009–2011; Microsoft SBA 2006; and Microsoft Office Accounting 2007–2009 using the free tool available at http://quickbooks.intuit.com

• Transfer data directly from Quicken 2012 through current version, QuickBooks 4.0 through current version, and Microsoft Excel 2000–2010.

Antivirus and firewall compatibility considerations include the following:

• Antivirus products (with/without firewalls) software compatibility

• Symantec/Norton Antivirus, Internet Security, Norton 360

• McAfee Internet Security, Total Protection, VirusScan Plus

• Trend Micro PC—cillin

• ZoneAlarm

• Windows firewall compatibility

• Windows 7 firewall (all versions)

• Windows Vista firewall (all versions)

• Windows XP firewall (all versions)

Express Installation of QuickBooks

Select the Express option (as shown in Figure 17.1) when you need to use QuickBooks from a single computer and you want to replace your original version of the software with the new software. QuickBooks Pro and Premier install the application and database manager in the C:Program FilesIntuitQuickBooks 2014 folder. QuickBooks Enterprise Solutions defaults to C:Program FilesIntuitQuickBooks Enterprise Solutions 14.0 folder.

By default, QuickBooks uses the following locations for storing your data file. If you have previously created a file, the default is the last known directory a file was created in, although you can select a different directory if desired:

• Windows—C:UsersPublicDocumentsIntuitQuickBooksCompany Files

• Windows 7—C:UsersPublicPublic DocumentsIntuitQuickBooksCompany Files

• Windows XP—C:Documents and SettingsAll UsersDocumentsIntuitQuickBooksCompany Files

• Windows Vista—C:UsersPublicPublic DocumentsIntuitQuickBooksCompany Files

QuickBooks prompts you to enter the license and product numbers; these numbers are provided on a bright yellow sticker on the QuickBooks CD sleeve, or if you have downloaded the software, you will receive an email with the license details. Follow the remaining instructions for installing the QuickBooks software.

Installing QuickBooks for Multiuser Mode

QuickBooks 2014 streamlines the process of installing QuickBooks for multiuser simultaneous access to your company data. As discussed in the introduction, when QuickBooks is going to be used in a multiuser configuration there must be one computer on your network that acts as the QuickBooks Server to host your data files and the QuickBooks Database Server.

It is not necessary to use an actual computer running Windows Server software as your QuickBooks host because QuickBooks runs equally well on peer-to-peer and client/server domain networks. When you install QuickBooks on a network, you should always perform the installation on the computer that functions as the server before installing QuickBooks on any workstations.

During installation, QuickBooks prompts you to select the installation type, as shown in Figure 17.1. Select Custom and Network Option for all network installations, including server (host) and workstations (clients). Follow the remaining prompts to select the installation options that best fit your network requirements for the specific computer you are installing. The choices include

• I’ll Be Using QuickBooks on This Computer—Use this if you are installing QuickBooks on a computer that will access only the Company files across the network.

• I’ll Be Using QuickBooks on This Computer, AND I’ll Be Storing Our Company File Here So It Can Be Shared Over Our Network—You will possibly run QuickBooks from this machine and have it act as your QuickBooks Server, which hosts your company files.

• I Will NOT be Using QuickBooks on This Computer...—You can elect to install only the QuickBooks Database Server and QuickBooks Database Server Manager Utility on the hosting computer. This install does not require a license.

During the install, you might be prompted to Validate your Intuit User ID, or be prompted to create one. This step is optional. If you choose to enter or create a new account, Intuit populates your registration for you. If you choose to skip entering your User ID you are then prompted to enter your license and product numbers. Follow all additional prompts to complete the install process.

When all your settings are correct for your network, click Install from the Ready to Install dialog box. As with the Express install, QuickBooks displays a progress bar indicating the status of the install process. The Congratulations dialog box displays when the installation is finalized. You can either launch QuickBooks to begin creating a new file or open an existing file or a practice file.

→ For more information, see “Getting Started with QuickBooks,” p. xx.

The QuickBooks Database Server Manager

Automatically installed is the QuickBooks Database Server Manager Utility, if you selected the option I Will NOT Be Using QuickBooks on This Computer. I Will Be Storing Our Company File Here So It Can Be Shared Over Our Network (A License Is Not Required For This Option). This utility launches automatically after the installation is complete.

Changing to Multiuser Access

If you initially installed QuickBooks for single-user access and want to change it to multiuser access, follow these steps to enable the Database Server Manager utility:

1. From the menu bar, select File, Utilities, Host Multiuser Access.

2. Click Yes to the Start Hosting Multiuser Access message that displays.

3. Click Yes to the Company file must be closed. If prompted, click Continue to allow Windows administrator permissions and Yes to allowing the changes to be made to your computer.

4. Read the information on the Multiuser Setup Information message that displays. Click OK to close.

QuickBooks installs the network database server as a Windows service/process, and it is configured to run under the QBDataServiceUser24 Windows user account. (This is the user account assigned to QuickBooks 2014.)

The QuickBooks Database Server Manager utility provides users access to configure the database server. It is critical that this utility run and be properly configured to permit multiuser simultaneous access to your QuickBooks Company data files.

Opening the QuickBooks Data Server Manager

Your computer might display the utility in your system tray in the lower right of your computer taskbar. If not, you can open the QuickBooks Data Server Manager by following these steps:

1. From the Windows Orb or Windows Start button (lower-left corner of Windows taskbar), click All Programs.

2. Scroll to find and select the QuickBooks folder.

3. Click the QuickBooks Database Server Manager. If prompted, click Yes to the User Account Control Message. The Database Server Manager is launched, as shown in Figure 17.2. The data path displayed in this figure might differ from your own data path.

Figure 17.2. The Database Server Manager is used to configure proper multiuser hosting.

The Scan Folders tab consists of two main panels, each performing critical functions. The first is to identify the various windows folders on the QuickBooks server computer that contain QuickBooks data files. To add folders, click the Add Folder button and use the Windows directory service display to locate all folders appropriate for your network configuration.

When all folders containing QuickBooks data files are displayed in the top panel, perform a scan of those folders to locate your QuickBooks files; these files then appear in the lower panel. A result of this scanning process, the Database Server creates a *.QBW.ND file for every QuickBooks Company file. This Network Description file contains critical information about the Database Server’s location on your network, including the server name and IP address. When a QuickBooks workstation accesses the Company file, the companion *QBW.ND file advises QuickBooks where and how to find the Database Server.

The QuickBooks Administrator should also configure the Monitored Drives tab, selecting the computer disk drive(s) containing the folders where QuickBooks Company files are located. The utility then monitors these drives to identify new *.QBW or *.QBA files that might be added to those drives and directories.

→ For more information about these named QuickBooks file types, see “Choosing a Method to Share Data,” p. xxx.

The Database Server tab displays information about the Database Server, the current QuickBooks Company file(s) in use, and the name of the user logged in. The Updates tab provides information about the current version of the QuickBooks Database Server; it is essential the Database Server version always match the QuickBooks version in use.

For QuickBooks to operate properly, you should never close the QuickBooks Database Server Manager unless you must also shut down the server for other reasons. A best practice is to minimize the utility instead.

Windows Permissions Required for QuickBooks

Simply installing QuickBooks for single-user or multiuser configuration does not guarantee that it will work properly. Certain Windows user permissions must be established to ensure proper access. All QuickBooks users must have full control, including read/write privileges for any directory from which Company data files will be accessed.

Setting Permissions in Windows 7 or Windows Vista

To properly configure Windows 7 or Windows Vista user permissions, follow these steps:

1. Using Windows Explorer, locate the folder containing your QuickBooks Company files.

2. Right-click the folder and select Properties.

3. Click the Sharing tab, and then click the Share button.

4. In the File Sharing window, use the drop-down list to select each user needing access to QuickBooks, and then click Add.

5. In the Permission Level column, click the Read drop-down list and select Read/Write.

6. Click the Share button to close the File Sharing window, and then in the Properties window, click the Security tab.

7. Click the Edit button for the Group or usernames section.

8. Select the QBDataServiceUser24 User.

9. In the Permissions for QBDataServiceUser24, select the Full Control checkbox in the Allow column, and click OK.

10. Click Close.

Setting Permissions in Windows XP Professional

To properly configure Windows XP Professional user permissions, follow these steps:

1. Using Windows Explorer, locate the folder containing your QuickBooks Company files.

2. Right-click the folder and select Properties.

3. Click Sharing, Share This Folder option.

4. Click the Permissions button.

5. In the Group or usernames section, select Everyone.

6. In the Permissions for Everyone section, select the Full Control checkbox in the Allow column

Setting Permissions in Windows 8

Before you begin sharing files and folders, make sure that you’re logged in to Windows as Administrator. Follow these steps to properly set permissions for sharing a folder residing in local drives or User Profile folders.

1. Open the Properties from the right-click context menu.

2. Click the Sharing tab.

3. From the Network File and Folder Sharing tab, click Share to open File Sharing.

4. Select Everyone from the drop-down menu and click Add. This will add the Everyone group in the File Sharing list, and allow you to change folder access permissions.

5. From the Read drop-down menu, select Read and Write Permissions to users.

6. (Optional) Remove the Everyone group from the File Sharing list.

7. Be certain that the QBDataServiceUser24User has been included in the permission settings.

8. Click Share to share the folder on the network.

Keeping Your Software Current

Intuit has a service discontinuation policy on its software. Live technical support and add-on business services such as payroll, credit card processing, QuickBooks Email, and online banking will be discontinued for versions three years or older.

Typically, you need to upgrade your software by May 31 for each year for versions of QuickBooks three years or older to retain these active services.

Upgrading Your QuickBooks Version

To keep your QuickBooks software supported, you need to upgrade at least every three years, or if you are like me, you may want to upgrade each year to take advantage of the newest features. I post a document on my website each fall detailing the newest features with each release. You can access this by visiting my blog and searching for “What’s New” in the blog title at www.quick-training.com/blog.

If you are using QuickBooks Enterprise and you subscribe to the annual Full Service Plan, you receive the newest software version each year, in addition to having access to elite technical support.

If you have used QuickBooks before, and then you purchase the newest year edition, you will be upgrading your QuickBooks file. QuickBooks Pro and Premier both can be upgraded to a QuickBooks Enterprise Solutions file. QuickBooks Enterprise Solutions cannot be downgraded to a QuickBooks Pro or Premier file.

The upgrade process is quite simple. If a prior installation of QuickBooks is detected, you have the option to Upgrade or Change Installation location. If you want to upgrade the prior version, select Replace the Version Selected Below with the Version I’m Installing Now. From the drop-down list, select the version you want to upgrade. The Windows path for your current version of QuickBooks displays.

However, if you want to install QuickBooks 2014 without upgrading any existing version, select the Change the Install Location option. Select a different location by clicking the Browse button and locating a new installation folder. If the folder you want to use does not exist, click the Create New Folder icon for your installation. Follow the instructions for completing the install process.

Installing a QuickBooks Maintenance Release

After a version is released, changes, improvements, and fixes are provided in the form of a maintenance release. Often, Intuit offers the release as a manual download only. This is usually during the testing phase, and you may not want to install the update in this phase.

To choose how QuickBooks detects whether a maintenance release exists, follow these steps:

1. From the menu bar, select Help, Update QuickBooks.

2. The Overview tab displays on the Update QuickBooks dialog box. Read the provided information. (Optional) Click Update Now if you want to complete the task.

3. Click the Options tab and select from the following:

• Automatic Update—If you select Yes, QuickBooks automatically downloads the update and provides a dialog box for you to install the update the next time you launch QuickBooks.

• Shared Download—If you work with multiusers accessing QuickBooks, you should select Yes to share the download with the others. This saves time and ensure that other users can access the newly updated file.

• Download Location—QuickBooks displays the location and name of the downloaded file.

4. You can then choose which updates to install:

• Payroll, Federal and State Forms Engine

• Employee Organizer

• Maintenance Releases

• Help and Other Updates

5. Click Save if you have made your changes, Revert to return to the original settings, or Close if you have not made any changes. (Optional) Click Help for more guidance.

6. Click the Update Now tab. From this tab, you can choose which updates to install, learn when they were last checked, and view the status.

7. (Optional) Select Reset Update to reinstall the entire update or select Get Updates.

8. A progress message displays. Click Close when finished.

To install the newest maintenance release you may need to close QuickBooks and reopen the software. You can also check the product information window from an open QuickBooks file by selecting F2 or pressing Ctrl+1 on your keyboard; then you can read the Product line at the top (see Figure 17.7).

Protecting Your QuickBooks Database

It is a good practice to protect your QuickBooks data and safeguard it against some unforeseen catastrophic event. In addition to making a backup of your data regularly, you might also consider including power protection, virus protection, and computer disk defragmentation.

Creating a QuickBooks Backup

Even with the best planning and safeguards, unforeseen catastrophic events can happen. Safeguard your company data by creating a regular backup.

Ideally, you should back up your company files at the end of each session. However, if you are working in a multiuser environment, you should routinely wait until the end of each workday to back up. If your computer has more than one drive, you can back up onto a different disk drive from the one where your QuickBooks data is normally stored. If you have only one hard drive, you should back up onto some type of removable media, such as a USB flash memory drive.

To make a backup of your QuickBooks data, follow these steps:

1. From the menu bar, select File, Back Up Company, Create Local Backup. The Create Backup dialog displays with Local Backup selected.

2. (Optional) Click the Options button. The Backup Options dialog box displays as shown in Figure 17.3. Setting these options enables preferences for this and future backups.

Figure 17.3. Create a backup of your QuickBooks file for safekeeping.

3. Choose the Browse button and browse to select a location to save your backup. It is recommended that you save your backup file to a location other than the location where the data is stored.

4. (Optional) Choose to Add the Date and Time, and to Limit the Number of Backup Copies in the selected folder.

5. Select the Complete Verification (recommended) option. For more information, see the section titled “Using the Verify Data Utility” in this chapter.

6. Click OK. If you have selected a location for the backup that is on the same drive where the data is stored, a warning message might display, recommending that you Change Location for the backup copy. Click Change Location (recommended) or Use This Location.

7. From the Create Backup dialog box, click Next.

8. Select Save It Now and click Next.

9. Type a filename for your backup, or accept the default filename.

10. Click Save.

Automating Your QuickBooks Backup

To create a schedule for automatically backing up your QuickBooks data file, follow these steps:

1. Follow steps 1–7 listed in the previous section titled “Creating a QuickBooks Backup.”

2. Select one of the following options: Save It Now and Schedule Future Backups or Only Schedule Future Backups. The Where Do You Want to Backup Your Company File? message displays if you have not previously set your file to backup automatically.

3. If you want QuickBooks to backup automatically when you close the file, you can define how many times you can close the company before the automatic back up is done. To do so, select Save Backup Copy Automatically....

4. To set a backup on a schedule, click the New button to display the schedule details as shown in Figure 17.4. Type a description for the backup, browse to the desired location, and define the number of Backup Copies to Keep.

Figure 17.4. Schedule an automatic backup of the QuickBooks file.

5. Specify the Start Time, how often the task is run, and on what specific days.

6. (Optional) Click Store Password if your computer is password protected. Click OK to return to the Schedule Backup dialog box.

7. Click OK to close the Schedule Backup dialog box.

8. Click Finish to save the schedule.

9. Click OK to close the confirmation message that a backup has been scheduled.

Restoring a Backup

If you find you need to restore your data from a backup, follow these steps:

1. From the menu bar, select File, Open or Restore Company.

2. Select Restore a Backup Copy. Click Next.

3. Select Local Backup. Click Next.

4. Locate the backup file and click Open.

5. Click Next after reading the message about Where Do You Want to Restore the File.

6. Browse to select the location and type a name to be given to the restored file. Click Save.

7. QuickBooks provides a progress bar as the file is being restored. Click OK to the information window that the file was restored successfully.

Protecting the QuickBooks Operating Environment

To prevent an unexpected or unattended shutdown, you should install an uninterruptable power supply (UPS) on each of your computers running QuickBooks, as well as all network components that connect those computers. An adequately sized UPS can prevent power blackouts, brownouts, and surges. A Smart-UPS that automatically begins closing open programs and powering down the computer prior to depletion of the UPS battery should be used when QuickBooks must be left running during unattended periods.

Power Protection

Even if a UPS protects your server and workstation, if the connecting network hardware (switches, hubs, or routers) are not protected, a sudden power event produces a loss of connection between the QuickBooks client and QuickBooks server, which might result in data corruption. It is recommended that all network hardware also be UPS protected.

Keep your computer safe by preventing power-related corruptions to your QuickBooks and not overloading your UPS with any non-essential computing components.

Virus Protection

The Internet has changed our lives, but one unfortunate side effect has been the contagion of computer viruses sent via email (or other data transfers). Because of the Internet, computers and networks are not immune from attack. Malware programmers look for weaknesses in the computers not only of big corporations, but small businesses and individuals, in order to steal financial information or create havoc and destruction of our computer applications. Because QuickBooks is the leading financial software on the market, it is a favorite target of many who would seek to invade Company files for personal gain.

Good behavior on the part of computer users is not enough to protect your QuickBooks data from attacks. Although surfing certain kinds of websites puts users at higher risks of infection, even legitimate websites can be compromised. Seemingly innocent results in a search engine can open hostile sites that immediately download a computer virus. Some of these viruses collect information from your computer (spyware) and some install malicious (malware) software, but they almost all disguise themselves just long enough to take down your data or your network. Even if you never surf the Web, you are still vulnerable as long as you have an open Internet connection or ever access a flash drive or CD.

A high-quality antivirus program is essential if you are running QuickBooks on your computer or over a network. There are effective programs that can run with a minimum of computer resources and blocks the overwhelming majority of threats. Set your antivirus program to run in an always-on mode and to perform a regular (preferably daily) scan of your computer. Ensure these programs are up-to-date; you should update your antivirus software to the most current virus definitions prior to each complete scan. Do not turn your antivirus software off unless you unplug your computer from the Internet and your network.

Many antivirus programs are sold in combination with a variety of Internet and network security features. Be advised that some of these features create additional layers of security that can restrict the capability to connect QuickBooks in a multiuser environment. A list of compatible antivirus and firewall programs is included in the “System Requirements” section of this chapter.

Windows Disk Defragmentation Utility

Fragmentation refers to any condition where data is not recorded in a logical continuous order. QuickBooks is subject to two different kinds of fragmentation—disk fragmentation and database fragmentation.

→ For more information on the latter, see “Database File Fragments,” p. xxx.

An example of disk fragmentation is when a QuickBooks invoice is recorded on your hard drive adjacent to a picture of your pets. Although it seems ridiculous to anyone who ever worked as a file clerk, this is the normal function of the Windows operating system.

Your QuickBooks company file can be fragmented on your computer’s disk drive just like any of your other files. This occurs when the operating system cannot create or insert new information into the file in one logically contiguous space. Because the Windows operating system does not restrict the writing of data to only contiguous areas of your disk drive, as the total amount of data on your disk drive increases, fragmentation also increases.

Because of fragmentation, the hard drive’s disk head must repeatedly move back and forth over the disk when reading and writing various parts of the file. The more the disk head must move from one area to another, the less efficient the disk drive is, lengthening the response time of QuickBooks trying to find your data.

To correct this type of fragmentation, we must force our Windows computer to rewrite all the parts of files to contiguous space on the hard drive. This is called defragmentation, and QuickBooks users should routinely run the Disk Defragmentation tool included with Windows software. After running this tool, QuickBooks users should see an increase in the speed of file access and data retrieval.

If your disk drive is less than 10% full, run defragmentation three or four times per year. If your disk drive is running at 25% to 30% of capacity, perform a monthly defragmentation. If your disk drive is at 50% or more of capacity or you have a QuickBooks Company file in excess of 50MB, you might need to run defragmentation once a week.

Defragmenting Your Windows 7 Computer

Choosing to defragment your computer can lead to a more responsive data file, providing quicker retrieval of data for reporting.

1. In the lower-left corner of your screen, click the Windows Orb or Start button.

2. Click All Programs.

3. Open the Accessories folder.

4. Open the Systems Tools folder.

5. Click Disk Defragmenter to open the utility displayed in Figure 17.5.

Figure 17.5. Run the defragmenter tool on your computer often.

6. Select the drive you want to defragment.

7. Click Analyze. A dialog box displays after the disk is analyzed, detailing if you need to perform the defragmentation.

8. To defragment the selected drive(s), click the Defragment Disk button. You can monitor the process in the Current status area (under the Progress column). After the process is complete, the results are displayed.

9. To display more detailed information, click View Report.

10. To close the Disk Defragmenter, click Close.

Optimizing Your Hard Drive in Windows 8

Windows 8 has an entirely different process that includes defragmentation called Optimize (of hard drives). This tool includes three types of optimization including Defragmentation, Trim optimization and Slab consolidation. The type of optimization the tool performs depends on the type of drive being optimized. Windows automatically chooses the optimization processes that are right for your drive(s).

Defragmenting rearranges fragmented data so a drive can work more efficiently. Trim optimization informs the backing storage which blocks of data are no longer in use; this allows the storage to optimize space and performance for future use. Slab consolidation, used primarily in thinly provisioned drives, rearranges data from sparsely populated slabs to densely populated slabs.

To optimize your hard drive:

1. Open the Search charm, type Defragment, and then tap or click Defragment and optimize your drive(s).

2. Pick the type of drive you want to optimize under Media type in the Optimize Drives dialog, and then tap or click Optimize and follow the on-screen prompts.

This process might take from several minutes to several hours depending on the size of the drive and degree of optimization needed.

Using Intuit Data Protect Online Backup Service

Intuit Data Protect is a subscription service that automatically backs up your company file(s) and optionally other important files from your computer. When you use Intuit Data Protect as your backup method, all your data needed to restore your company file is saved to Intuit’s secure server. The backup files created by Intuit Data Protect are stored for 45 days, enabling you to not only restore the most recent backup but also any prior backup during the past 45 days.

After you have set up your Intuit Data Protect subscription you do not need to establish a backup schedule or perform manual backups. Intuit Data Protect performs a once-a-day backup of every QuickBooks file you have configured for backup. This is unlike the manual backups, which should be done when no one is working in the file. The Intuit Data Protect backups run in the background, enabling you to continue working normally in QuickBooks. If a scheduled backup is missed because your computer is off or can’t access the Internet, Intuit Data Protect starts a backup as soon as your computer is turned back on or Internet service becomes available.

To restore a backup made using the Intuit Data Protect Backup service, follow these steps:

1. From the menu bar, select File, Back Up Company, Open Online Backup: Intuit Data Protect.

2. From the Backup Status dialog box, click Restore from Backup.

3. Select the File you want to restore, and select the Version (Date and Time) of the backup. Click Continue.

4. Browse to the location where you want your file restored to (original or new location). Click Select.

Intuit Data Protect begins the process of restoring your file(s) to the designated location. This process can take a substantial period of time depending on the size and number of files you are restoring. Do not attempt to close either Intuit Data Protect or QuickBooks during the restore process.

5. Upon completion, Intuit Data Protect displays a message that the restore completed. Click OK to close the dialog box.

Monitoring Your QuickBooks Database

Because QuickBooks is composed of a series of tables that store your accounting records, it is important to monitor your data file from time to time to ensure your data has integrity and is the proper size and configuration. There are a couple of methods in QuickBooks to monitor the database, including the Product Information dialog box and the Verify Data utility.

The Product Information Dialog Box

The Product Information dialog box provides valuable information about the health of your QuickBooks file. From an open QuickBooks file, press Ctrl+1 or F2 on your keyboard. The Product Information dialog box provides a wealth of information, including version, product license number, versions used, and much more. See Figure 17.6.

Figure 17.6. With QuickBooks open, press Ctrl+1 on your keyboard to view useful information about the file. Here, you can see the number of Data File Fragments shown.

File Size

In Figure 17.6, about halfway down the left side is a section that provides data about the File Size, Total Transactions, Total Targets, Total Links, and DB File Fragments. This information can help you judge whether your data file is becoming too large for efficient processing of transactions and reports.

Many experts debate over the maximum file size of QuickBooks data, and Intuit has not published any formal guidelines regarding file size limits.

To calculate the anticipated growth of a data file, follow these steps:

1. Take the number of transactions each month times 2KB to get the monthly growth rate.

2. Take that amount times 12 for the annual KB growth per year.

3. Divide that amount by 1024 for the number of MB per year.

Typically, the file should grow by 30MB per year or less for Pro or Premier and 50 MB per year or less for Enterprise to maintain reasonable performance. Specialized condense features are available in the QuickBooks Accountant editions that can help in removing data from prior years; contact your accounting professional for this service. The age and processing speed of the computer, along with other factors, may also affect the software’s performance.

Factors such as the number of lines of detail for each transaction significantly increase the number of links per transaction—this factor alone can greatly impact the size of the database. Based on personal experience, QuickBooks Pro or Premier data files in excess of 200MB or QuickBooks Enterprise files in excess of 450MB might experience sluggish performance. QuickBooks file performance is impacted by so many variables; it is not possible to name specifics.

Database File Fragments

As mentioned earlier, file fragmentation can negatively affect the performance of QuickBooks. Because file fragmentation is a normal part of using Windows, we must assume some fragmentation is also normal. The question becomes how many file fragments is too many? Some experts say more than 10 fragments is too many, others say 20, and still others say 50.

As a general rule, if the file is performing without issue and file fragments are fewer than 50, you don’t need to be alarmed; however, anytime your file exceeds 50 fragments, it is time to reduce fragmentation.

→ For more information, see “Reducing Database File Fragments,” p. xxx.

QuickBooks List Limitations

As shown previously in Figure 17.6, the Product Information dialog box includes the List Information section. This section includes the size of the various QuickBooks lists (both active and inactive entries) and is another important database statistic to monitor. QuickBooks lists have been preconfigured to limit the number of entries the database can support.

One of the most significant limitations deals with the number of names you can have in QuickBooks Pro or Premier—the combined limit of customers, employees, vendors, and other names is 14,500. QuickBooks does not permit you to delete any names that have been used in a transaction, and the list limits include both active and inactive entries. This list size restriction can be a serious limitation to a growing business. According to Intuit, QuickBooks Enterprise can support up to 1 million combined names, although they note some performance degradation is likely as you approach this upper limit.

Similar list limits exist for the Items list—14,500 in Pro or Premier and 100,000 from a functional standpoint for Enterprise. Most other lists in QuickBooks Pro, Premier, or Enterprise are limited to 10,000 entries. One noted exception is the Price Levels list, which is limited to 100 entries in Pro and Premier and 750 entries in Enterprise.

For QuickBooks Enterprise Solutions 14.0, review Table 17.1 for these specific lists limits:

Table 17.1. QuickBooks Enterprise 14.0 Larger List Limits

More details on the list limits between the versions of QuickBooks can be found by searching for “List Limits” on the Intuit support website at http://quickbooks.com/support.

Using the Verify Data Utility

Although the Verify Data utility can detect many forms of data corruption and test the integrity of the database, it also can be used to monitor the health of your Company file. Select this option when creating a backup of your QuickBooks Company file.

Additionally, if you use an external backup or Intuit Data Protect, run the Verify Data utility periodically to check the integrity of your Company file.

Monitoring QuickBooks Performance Using Windows Task Manager

Have you ever encountered the Windows alert that QuickBooks is Not Responding? This can occur during any prolonged operation, such as compiling a lengthy or complex report, running one of the internal utilities, or even adding entries to large lists. This usually is a problem with the way Windows reports activity, rather than with QuickBooks itself. Give the process time and do not get in a hurry even if you think the process has stopped working.

Follow these steps to see if QuickBooks is still properly working by viewing the process in Windows Task Manager:

1. Place your cursor on an empty section of the Windows taskbar (typically at the bottom) of your computer. Right-click, and then select Start Task Manager.

2. From the Windows Task Manager, select the Processes tab.

3. Place a checkmark in the box Show Processes from All Users, located near the bottom of the list of processes.

4. Click the header of the Image Name column to sort the list of processes in alphabetical order.

5. Scroll down the list to the process QBW32.EXE. Observe both the numbers listed in the CPU and Memory columns. Watch this process for a minute or more, if either or both of these columns are changing even slightly, QuickBooks is still active and working.

6. To close the Windows Task Manager, click the X in the top-right corner.

After verifying that the process is running and the information referred to in step 5 is valid for your file, you can feel confident that when the task you started is finished, QuickBooks returns to full operation and no longer displays the (Not Responding) windows message.

QuickBooks Database Corruption

On occasion, your QuickBooks Company file can become corrupted. Database corruption is any damage to a database that impacts its integrity or functionality.

Common Causes of Corruption

Database corruption can result from improper shutdown of QuickBooks, as well as power issues (surges, spikes, and outages). It can also occur as a result of a fatal application (program) error. System hardware, such as bad disk drives or raid controllers, can corrupt data. Your operating system can produce QuickBooks data fragmentation.

In a multiuser environment, networking components (such as wireless routers) might produce corruption because their failure can break the connection between the Application and Database Server.

A large QuickBooks file size does not necessarily cause data corruption, although there is more data in which corruptions can occur. Larger files also have more fragmentation and performance issues if your hardware is not meeting the reported minimum requirements. Consider adding additional computer resources to support your large QuickBooks file and reduce potential data size associated corruptions. For more information, see the section titled “System Requirements” in this chapter.

Signs of Data Corruption

QuickBooks might report data corruption by displaying specific problem messages or error codes. Often the first sign of data corruption does not appear until a QuickBooks user sees an accounting transaction irregularity—for example, when an out-of-balance balance sheet or accounts receivables or payables subledger reports don’t reconcile to the general ledger.

If you run the Verify Data utility and your Company file has lost integrity, QuickBooks displays a warning such as the one shown in Figure 17.8, indicating you should run the Rebuild Data utility. In addition to the displayed message, additional information is provided in the QBWin.log file.

Figure 17.8. Some database corruption errors require using the Rebuild Data utility.

The QBWin.log File

If the Verify Data utility detects an error in your data, you might want to get more information about the specific problem(s) within your data. QuickBooks records operational data, including information related to the Verify Data and Rebuild Data utilities within a log file called the QBWin.log.

To view the QBWin.log file, follow these steps:

1. With your QuickBooks Company file open, press the F2 key (or Ctrl+1) to display the Product Information dialog box.

2. Now press the F3 key (or Ctrl+2) to display the Tech Help dialog box.

3. Select the Open File tab.

4. Scroll down the list and click the QBWIN.LOG file; then click Open File. The file opens in Windows Notepad (see Figure 17.9).

Figure 17.9. Review the QBWIN.LOG to determine errors that might be causing the error messages.

5. The Verify Data section should be near the bottom of the log, so scroll to the bottom and then scroll back up until you find the Verify portion of the log file, which begins with the following:

= = = = = = = = = = = * BEGIN VERIFY LOG * = = = = =

The QBWIN.LOG file will end with:

= = = = = = = = == = = * END VERIFY LOG * = = = = = =

You can scroll through the log file to view errors detected with the file. I recommend that you contact Intuit technical support or a local QuickBooks professional for reading and correcting any errors listed.

The QBWIN.LOG is a snapshot of technical details during the current session of QuickBooks. It contains information about how the program performed when it was started, if it successfully connected with your Company file, and any errors experienced during operations or while performing the verify/rebuild utilities. Many different messages might be contained in your QBWin.log; some might represent minor errors and others might represent more severe forms of database corruption. The QuickBooks Support website contains many knowledge-based articles that correspond to the various errors. A few common error messages and possible solutions have been included in this section.

You might want to complete a rebuild of the company file after performing the suggested repairs.

→ For more information, see “Rebuilding Your Company File,” p. xxx.

Error: Verify Memorized Report List...

A corrupted Memorized Report is usually associated with this error. QuickBooks normally repairs this problem if you re-sort the Memorized Report List followed by running the Rebuild Data Utility. To do so, follow these steps:

1. From the menu bar, select Reports, Memorized Reports, Memorized Report List.

2. In the Memorized Report drop-down list, select Re-Sort List.

3. To continue, click OK to the Re-sort prompt.

In some cases these steps will not correct the corruption; it then becomes necessary to “delete” the affected memorized report, then re-sort the list, and rebuild the data file.

Error: Verify Names List (Such As Customers)...

This form of corruption occurs when one of the Names lists has a database index error. Try re-sorting the Names List followed by running the Rebuild Data utility to resolve this type of error.

To access the Names List, follow these steps while logged in to the file in single-user mode:

1. From the menu bar, select Banking, Write Checks.

2. Place your cursor in the Pay to the Order of field.

3. On your keyboard, press Ctrl+L.

4. The Name List displays. In the Name drop-down list, select Re-sort List.

5. In the Re-sort List? message, click OK.

Error: Verify Name (Specific List): Duplicate Name Encountered...

QuickBooks might encounter a duplicate name. This might occur if you import data using the IIF (Intuit Interchange Format) import file type or you use a third-party application. Frequently one name might be marked as inactive, or QuickBooks might have inserted an asterisk (*) in front of the duplicate name.

To locate and merge the duplicate names, follow these steps:

1. Find the names that are duplicated.

2. Edit one of the names to first change it to be unique; if QuickBooks has inserted an asterisk (*) remove that character from the name as part of this change. Save the new name.

3. Return to the name you just changed and edit it again to change it to be identical to its duplicate; then click Save.

4. QuickBooks displays the Merge dialog box, as shown in Figure 17.10. Confirm the merge of both names by clicking Yes.

Figure 17.10. Certain lists in QuickBooks will let you merge duplicate names.

5. Run the Rebuild Data utility.

Error: Verify Target: Transaction Out of Balance...

This error usually results when the target record (such as the check detail lines) does not equal the amount of the source record (such as the check total).

To correct this type of error, follow these steps:

1. Open the transaction identified in the QBWin.log report.

2. Verify whether the amount in the header (such as check amount) is identical to the total of the amounts in the detail lines. If these amounts are different, you need to correct the erroneous amount(s).

3. Save the corrected transaction.

4. Run the Rebuild Data utility.

Error: Verify Target: Invalid Open Quantity...

This error can result when the link between an Estimate and Sales Order and its associated Invoice (for partial quantities) is broken.

To relink the transactions, follow these steps:

1. Open the transaction, reported in the QBWin.log.

2. Add a period (.) to an empty detail line of the transaction; this will not change any values for the transaction. However, doing so can help relink the source and target transactions.

3. Save the transaction.

4. Run the Rebuild Data utility.

→ For more information, see “Rebuilding Your Company File,” p. xxx.

Repairing List Corruptions

Several of the symptoms associated with data corruption, and QBWin.log messages detailed previously, involve problems with the various QuickBooks lists. For many lists, you can re-sort the list simply by opening the list and clicking the list’s menu button located at the bottom of the list (such as Items). Prior to actually resorting the list, check the box labeled Include Inactive (if available) and select the option to change the list view from Hierarchical to Flat (not all lists offer a hierarchical view).

One of the major lists in QuickBooks is a hidden list called the Name List, which is a combined list of all the names (customers, employees, vendors, and other names).

To sort all the name lists at once, follow these steps:

1. From the menu bar, select Banking, Write Checks.

2. Place your cursor in the Pay to the Order of field.

3. On your keyboard, press Ctrl+L.

4. The Name List displays. From the Name drop-down list, select Re-sort List.

5. In the Re-sort List? message, click OK.

Reducing Database File Fragments

The highly compressed QuickBooks Portable Company File can be useful in dealing with some forms of index corruption as well as fragmentation. When a Portable Company (*.QBM) File is restored, QuickBooks re-creates all indexes associated with the database. In addition, the extreme level of compression applied when the file is produced eliminates file fragments. Therefore, creation and restoration of a Portable File might resolve many file-related issues.

Follow these steps to create and restore a Portable Company File:

1. From the menu bar, select File, Create Copy. The Save Copy or Backup dialog box displays, as shown in Figure 17.11.

Figure 17.11. Create and restore a Portable Company File to help reduce database file fragments.

2. Choose Portable Company File and click Next.

3. Accept the default location and filename or select a location to save the file and type a desired name for the file.

4. A few individual progress messages display; click OK on each of them.

5. Upon completion, QuickBooks displays an Information message indicating the file has been saved and details the designated location. Click OK.

6. To restore the Portable Company File, select File, Open or Restore Company from the menu bar.

7. Select Restore a Portable File and click Next.

8. Select the Portable Company File (ending in .QBM) you saved to the desktop (or your alternative location), and click Open.

9. In the Open or Restore Company dialog box, the Where Do You Want to Restore the File dialog displays. Review the message details regarding overwriting your current file and click Next, as shown in Figure 17.12.

Figure 17.12. Review this information before restoring your Portable Company File.

10. Click Save and select a save location. Because you are intending to replace your current file, select your current file location and current filename.

11. Click Save. QuickBooks replaces your current file with the data restored from the QuickBooks Portable Company File.

Rebuilding Your Company File

The Rebuild Data utility can repair many QuickBooks Company file issues. The utility attempts to repair or update data found to be corrupted, which might include permanently deleting transactions or list entries that are damaged beyond repair or that compromise the overall integrity of the file.

Before running the rebuild utility, you should gather information for comparison after the rebuild is complete. This should include all summary reports, including the Balance Sheet Standard and Profit & Loss Standard Report. You might also want to process additional reports, such as those for payroll details or checkbook registers, and so on.

To use the QuickBooks Rebuild Data utility, follow these steps:

1. If you use your file in a multiuser install, you need to log in to the file in single-user mode. From the menu bar, select File, Switch to Single User.

2. From the menu bar, select File, Utilities, Rebuild Data.

3. QuickBooks displays a warning message requiring a backup of the company file before actually starting the rebuild process. Follow the prompts to save a backup copy of your data.

4. When the backup is completed, the Rebuild Data utility starts. The time required to rebuild the database can vary depending on the size of your Company file. It is extremely important to permit the utility to finish completely.

5. QuickBooks displays a Rebuild Has Completed message. Click OK.

After the Rebuild Data utility finishes, the data file might still have corrupted transactions that the first pass of the rebuild did not correct. It might be necessary to rebuild your data more than once. As a general rule, if the utility does not resolve an issue within three passes, the database error cannot be fixed using the Rebuild Data utility.

Although severely damaged transactions are usually removed during the rebuild process, you might need to manually correct or delete other transactions that Rebuild Data could not correct, and then reenter them. Transactional data removed or corrected is listed in the QBWin.log file along with corrupted transactions, which the Rebuild Data utility cannot correct.

For severe cases of database integrity issues, you need to contact Intuit’s Technical Support.

Using the QuickBooks File Doctor

The QuickBooks File Doctor tool helps to diagnose and repair several forms of damage that prevent company files from opening. The tool must be run locally on a corrupted QuickBooks file; you cannot run this tool across a network or a mapped drive.

The QuickBooks File Doctor tool replaces and provides improved problem-solving features over the formerly available QuickBooks Company File Diagnostic tool.

The Company File Doctor tool requires the following:

• Internet connection.

• Company file must be smaller than 2GB.

• You must be logged in as the Administrator to run the network diagnose functions.

• QuickBooks must be in hosting mode. To start hosting from the menu bar, select File, Utilities, Host Multiuser Access.

• If Windows User Account Control is turned on, the File Doctor tool will be relaunched with elevated administrator privileges.

• Network diagnose and repair functionality may not work properly if multiple QuickBooks versions are installed on your computer.

Download the tool from QuickBooks technical support site: http://support.quickbooks.intuit.com/support/articles/HOW12723. The referenced knowledgebase article includes general instructions for using the tool.

1. Install the downloaded QuickBooks Company File Diagnostic Tool, following the instructions provided by Intuit within the knowledgebase article.

2. Click the installed icon to launch the tool. If you receive a message that the tool will be relaunched with elevated permissions, click OK. Click Yes to the User Account Control message. Figure 17.13 shows the dialog box that displays.

Figure 17.13. Use this tool to diagnose and repair a QuickBooks Company file.

3. Click the Browse button to select the QuickBooks Company (*.QBW) file or QuickBooks Accountant’s Copy File (*.QBA) that needs to be diagnosed and/or repaired.

4. Click Diagnose File. If required, enter the Admin password. Click OK. QuickBooks displays a Diagnosing Company File progress bar.

5. Select Yes if you access your company file from other computers on the network or No to the same message. When the tool has finished the process, there are three possible outcomes:

• Problem was found and was fixed.

• Problem was found but was not fixed. Instructions are provided for different methods of handling this.

• No problem was found. Instructions are provided for additional tools to try, including detailed instructions for network connectivity issues.

The File Doctor also offers advanced settings that can help with the following errors, although it is recommend you use these advanced settings only with the assistance of Intuit’s Technical Support.

• -6130 Errors—These errors often occur when you are opening a company file and the database server terminates.

• Data Sync Errors—These errors are related to the QuickBooks Sync Manager that manages the data housed in your desktop software and the data maintained in the “Intuit Cloud” for those applications you have granted this access.

Resolving QuickBooks Program File Corruption Issues

Some corruption can be the result of problems with the QuickBooks program files. This can occur during installation. A conflict occurs with some required component, such as Microsoft.NET or even the Windows Registry. Generally, QuickBooks runs normally but at some point the program experiences fault errors or might not install an update properly. Usually, these problems can be resolved by repairing the QuickBooks installation.

To repair your QuickBooks program files, follow these steps:

1. From your Windows taskbar, click the Windows Orb or Start button, and select Control Panel.

2. In the Control Panel, double-click Programs.

3. In Programs, select QuickBooks and choose Uninstall/Change.

4. Select Repair and click Next. QuickBooks begins the repair, displaying a progress bar during the process.

5. When the repair is complete, click Finish.

This procedure usually, not always, resolves file issues. In the event that it does not work, uninstall QuickBooks and then reinstall it. Use the preceding steps to remove QuickBooks by selecting the Uninstall option rather than Repair.