Troubleshooting: Getting a limited number of records or few of the records is missing in 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 seems few records are missing.

      We have a number of 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 shows 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 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 VB Demo from: Start >> All Programs >> QODBC Driver for use with QuickBooks >> VB Demo

VB Demo opened.

From the menu bar select "Connections" -> "Add a new connection"

Connect to DSN Select the "Machine Data Source" tab -> select the System DSN named "QuickBooks Data" -> Click "OK".

VB Demo connected.

Write "sp_optimizefullsync ALL" command in VB Demo & execute it by clicking "Query" button.

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

Optimizer building process started.

Optimizer building process finished.

Or ( For Selected tables)

Close all your application
Start VB Demo
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