[QODBC-Desktop] Getting a limited number of records or few of the records is missing in QODBC Table result.
Posted by Rajendra Dewani (QODBC Support) on 20 September 2012 02:58 PM
Troubleshooting: Getting a limited number of records or few of the records is missing in QODBC Table result.
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.
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.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.
Optimizer building process started.
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 finished.
Or ( For Selected tables)
Close all your application
Keywords: qb odbc slow, multiple web queries into excel slow running, excel running background query slow, qodbc and excel connection speed