Troubleshooting: Getting a limited number of records or a few of the records are missing in the QODBC Table result.

Problem Description

     1. When I Query my tables e.g. Bill or Customer, I noticed QODBC is not returning all the records, and it seems a few records are missing.

      We have several invoices that don't exist in the linked invoice table. The invoices can be pulled up in QuickBooks, but searching for the invoice number in the RefNumber field doesn't return any results.

    2.I am able to create an IRL and IR using QODBC and able to see the IR in QB. But when I am running the VB DEMO or running SQL using QODBC, it says record not found. I am unable to retrieve anything thing that I added after 11/18. But Can see the data in QB and process it in QB.

I also tried creating a Bill in QB and tried to use a simple SQL to run in VBDEMO nothing showed up.

Solutions

The reason could be QuickBooks Company file requires Rebuild/Verify, or QODBC cache file/Optimizer is corrupted, or some mismatch happened in the cache file.

Try the below-mentioned steps to see if it fixes the issue  

1. Rebuild your company file

To Rebuild QuickBooks company file, please select File->Utilities->Rebuild Data option:

Please make sure you follow the instruction of QuickBooks and make a backup before rebuilding.

2.Delete Optimizer file(s)

Close all applications other than QuickBooks/Reckon.

Follow steps from How to Remove Optimizer file

Verify the optimizer file(s) are removed.

Optimizer file can be found at "%appdata%\QODBC Driver for QuickBooks\Optimizer"
For Example: C:\Users\YourName\AppData\Roaming\QODBC Driver for QuickBooks\Optimizer

3. Rebuild Optimizer file

Close all applications other than QuickBooks/Reckon.

Start QODBC Test Tool from: Start >> All Programs >> QODBC Driver for use with QuickBooks >> QODBC Test Tool

Note:  QODBC Support Wizard is used to test QODBC SQL queries only and is not a development tool.

To Connect to DSN, Select the "QuickBooks Data" -> Click "Connect".

QODBC Test Tool connected.

Write "sp_optimizefullsync ALL" command in QODBC Test Tool & execute it by clicking "Run" button.

The sp_optimizefullsync command will bring some data to a local cache to increase retrieval performance for queries. Please let run the above command entirely. The above command may take some time, depending on your records in QuickBooks. It may take 4-24 hours or more, depending on the number of records in the QuickBooks company file. Please let this command run completely. Do not kill this command. It is not advisable to kill/close the application while building the Optimizer file.

Click on the "Run" button to start optimizing the records.

The optimizer building process started.

Optimizer building process finished.

Or ( For Selected tables)

Close all your application
Start QODBC Test Tool
Connect to your DSN
execute the below command
sp_optimizefullsync Bill
sp_optimizefullsync Customer

Also, Refer

VB Demo Missing

Troubleshooting: Cannot Get Full Records From QuickBooks

How to switch OFF or RESET the QODBC Optimizer

Keywords: QB odbc slow, multiple web queries into excel slow running, excel running background query slow, qodbc and excel connection speed