Banking optimize

Troubleshoot QuickBooks data file connectivity issues

QuickBooks data file connectivity errors can be a bear to troubleshoot. Jack Wallen suggests various troubleshooting tips.

When Intuit's QuickBooks works, the SMB accounting software is incredible; when it doesn't work, it's an absolute nightmare. Networking issues, firewall issues, corrupted data files, and more can quickly plague your QuickBooks installation. Oftentimes, the errors and warnings offered by the software are cryptic or not well documented. The next time you're unable to connect to your QuickBooks data file, try one of the solutions outlined below for getting your company file up and running.

Caution: You should always have a daily backup of your QuickBooks data file. In a worst case scenario, if your file becomes corrupt, you could restore from the previous day's backup and only lose one day's work.

How to troubleshoot data file connectivity

The QuickBooks Connection Diagnostic Tool is a small application that will test the connection between client and database server manager and help you to understand what is happening. In some instances, it will set the correct firewall rules on a client machine so QuickBooks can get through to the network.

After you install this tool, fire it up, click Browse, locate your company data file, and click Test Connectivity. If you don't get green check marks, you know there's an issue connecting to the database server. (The Advanced section allows you to run IP Reverse lookups, check the DB service status, and more.) If you are prompted to allow the diagnostic tool to open the firewall ports, do so --the diagnostic tool is quite good at opening those ports. If you get an error and the diagnostic cannot connect to your database server, you'll need to work on the server.

Database server manager

Most likely, all you have installed on your server is the QuickBooks database server manager. With this tool, you can instruct the service where to monitor for data files. If you do not let the QB database server know where the data files are, those files will not be made available to clients. To make everything a lot easier, place your data files on a shared folder on the root of C:\\ (such as C:\\Data or C:\\QB) and make sure everyone who needs access to the file has full rights. Then open the QuickBooks database server manager and click Add Folder (Figure A). Figure A

The Database Server tab will tell you if your db server is running or stopped.

Once you add the folder, click the Scan button and all data files within that folder should appear in the QuickBooks company files found area. If not, double check to make sure the data files are in the folder.

If your QuickBooks database server isn't running, follow these steps:

1. On your server, click Start and type services.msc in the run area.

2. In the Services window, search for the QuickBooks server manager (QuickBooksDB20 for newer versions).

3. If older database managers are running, stop them and set them to Disable.

4. Make sure the service is set to Automatic.

5. In the Recovery tab, make sure first, second, and subsequent failures are set to Restart The Service and click OK (Figure B). Figure B

Click the image to enlarge.

6. Right-click the service and select either Start or Restart.

7. Once the service has started (or restarted), go back to the QuickBooks database server manager and rescan the folder.

8. When the files appear, go back to one of the clients and attempt to connect. If you still get a connection issue, here are some troubleshooting tips:

  • Make sure only the server is hosting QuickBooks. If any client machine has hosting turned on, this can cause connection issues.
  • If you have not done so, copy the data file to a client machine and try to log in to the data file. If you can, verify and rebuild the data file. Once this is done, create a portable copy of the data file and then copy that portable copy back to the server. Now, restore from that portable file onto a client machine, and hopefully you'll get a connection restored.
  • Check your .tlg file (transaction log file) associated with your data file. If that file is significantly larger than your data file, create a portable copy of the data file and then restore that data file with a new name. This will reset the .tlg file.
  • If you're still having trouble connecting to the data file, try very temporarily dropping the firewall on the server and the client. If this allows the connection through, turn the client firewall back on and try to reconnect. If you still cannot connect, you most likely need to add exceptions in your server firewall. Remember that once you've managed to get QuickBooks through the firewall, bring that firewall back up on the server. For a listing of the specific ports to let through, read this QuickBooks knowledge base article.

On occasion, the problem with the data file connection is the server. When this happens, I move the hosting of the data file to one of the client machines until the issue with the server is resolved. Although it's far from ideal, this quick fix can at least get your end users working with QuickBooks until you can find a resolution with the server. In extreme cases, I have seen where the server was compromised, and just short of a re-install of the OS would allow QuickBooks clients to get into the data file; these are perfect circumstances for moving the data file to another machine. If you do this, you have to follow through with these steps on the new host:

1. Create a shared folder on the new host.

2. Copy the data files into the shared folder.

3. Open QuickBooks on the new host.

4. Click File | Utilities | Host Multi-User Access.

Now on each client you should do the following:

1. Map the newly hosted directory on the client.

2. Open QuickBooks.

3. Open a company data file and point it to the file in the mapped directory.

Prevention over repair

Here are some of my best tips for keeping your data file in tip-top shape, so hopefully you won't need to fix a data file connection in the future.

  • Clean your data: If you click File | Utilities, you will see an entry called Clean Up Company Data... -- you should use this tool monthly or quarterly to keep a neat and tidy data file. The clean up utility condenses closed transactions into summary journal entries, removes unused list elements, and creates a journal entry for each month in the selected period.
  • Verify and rebuild your data: The rebuild tool clears out errors (errors that are almost inevitable with QuickBooks). You must run the tool monthly or quarterly by going to File | Utilities, doing a verify, and then rebuilding your data file. This will go a long way to preventing data file corruption.
  • Log out of QuickBooks: If you are going to be away from QuickBooks for more than 15 minutes, close the application. Why? If your machine experiences a loss of network connectivity, and you are logged in to your data file, you can wind up with a corrupted data file.

More QuickBooks posts on TechRepublic

About

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website getjackd.net.

3 comments
seachange75
seachange75

Data connection issues with Quickbooks can be so frustrating, so these tips can really lower the stress level when trying to resolve this type of issue. In almost every instance that I've had data connection issues, I've had to create a portable copy and restore from that portable copy and every resolved fine. Very helpful if your new to this type of issue and are ready to pull your hair out!