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 notice QODBC is not returning all the records, and it seems a few 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 can create an IRL and IR using QODBC and see the IR in QB. But when I run the VB DEMO or SQL using QODBC, it says record not found. I am unable to retrieve anything thing that I added after 11/18. But I Can see the data in QB and process it in QB.

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

Solutions

The reason could be that the QuickBooks Company file requires Rebuild/Verify, the 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 the QuickBooks company file, please select File->Utilities->Rebuild Data option:

Please follow the instructions of QuickBooks and make a backup before rebuilding.

2. Delete Optimizer file(s)

Close all applications other than QuickBooks/Reckon.

Follow the steps from the How to Remove Optimizer file

Verify the optimizer file(s) are removed.

The 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 the 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 only to test QODBC SQL queries and is not a development tool.

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

QODBC Test Tool connected.

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

The sp_optimizefullsync command will bring some data to a local cache to increase query retrieval performance. 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 entirely. 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.

The optimizer building process is 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