Knowledgebase: TroubleShooting
[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 a few of the records are missing in the QODBC Table result.

Note: VB Demo is deprecated.

Please refer to How to use the QODBC Test Tool for testing

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

(1 vote(s))
Helpful
Not helpful

Comments (8)
Brandon S
16 January 2014 07:00 AM
Does the QODBC evaluation have the ability to do a complete extraction? Or is it limited in the number of records it can import? We are having issues with getting complete records and i want to make sure the demo is not the problem. We are a non profit and cannot spend money without understand if there is a need to. Thanks for your answers...
Jack
08 July 2014 09:58 AM
Hi Brandon,

Yes, QODBC has the ability to do a complete extraction. You can import QuickBooks data without any limitation. The evaluation version of QODBC is limited to 30 days of use and 20 write operations to QuickBooks per session, otherwise fully functional including server mode.
saim asif
27 December 2014 01:44 PM
I'm unable to retrieve any row from QB's employee/customer table. When i select from entity only vendors are returned. Can anyone help me resolve this issue.
Jack
29 December 2014 10:37 AM
Hi Asif,

Have you tried to run "sp_optimizefullsync ALL" command?
If you have not run, then I kindly request you to please run below command & try again:

sp_optimizefullsync All

This command will bring some data to a local cache to increase retrieval performance for queries. I would like to suggest you to please let run above command completely. Above command may take sometime depends on your records in QuickBooks. It may take 2-5 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 its building the Optimizer file.

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

I kindly request you to share more information about the issue you’re facing, so that we can locate the problem quickly.

We may need following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About (Start>>All Programs>> QODBC Driver for use with QuickBooks>> QODBC Setup Screen >> About Tab )
2) Screenshot of the issue you’re facing.
3) Share QuickBooks Version details: Press f2 on QuickBooks UI & share screenshot.
4) Share the SQL statement you’re using.
Share Entire Log Files as an attachment in text format from
5) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
6) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Daniel Belef
13 May 2015 07:06 PM
I am having the same issue. Access originally returned all customers from the customer table, then as SOON as I made a change to one of the customers for my first test of read / write, the next update returned only 81 customers for the same table. What is reason for this, how can it be fixed? Workin on it. Any info on why? It does NOT seem to be a rebuild issue, or shouldnt be, as this happened immedialty upon chaing one record.
Jack
14 May 2015 09:16 AM
Hi Daniel,

Have you tried to run "sp_optimizefullsync ALL" command?

If you have not run, then I kindly request you to please run below command & try again:

sp_optimizefullsync All

This command will bring some data to a local cache to increase retrieval performance for queries. I would like to suggest you to please let run above command completely. Above command may take sometime depends on your records in QuickBooks. It may take 2-5 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 its building the Optimizer file.

There might be optimizer file got corrupted, due to this you are facing the issue.

Please refer below mentioned article for How to verify if Optimizer file corrupted or not:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2636/44/troubleshooting---how-to-verify-if-optimizer-file-corrupted-or-not

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

I kindly request you to share more information about the issue you’re facing, so that we can locate the problem quickly.

We may need following information, I kindly request you to attach below listed files when replying to the ticket.

1) Screenshot of QODBC Setup Screen -- > About (Start>>All Programs>> QODBC Driver for use with QuickBooks>> QODBC Setup Screen >> About Tab )
2) Screenshot of the issue you’re facing.
3) Share the SQL statement you’re using.
Share Entire Log Files as an attachment in text format from
4) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
5) QODBC Setup Screen -- > Messages -- > Review SDK Messages
AK
06 April 2017 06:01 PM
I'm seeing a strange issue. After report is done running (providing inconsistent results), we still see quickbooks process in task manager taking 50% of CPU for several more hours. What could be causing this (uninstalled qodbc drivers, ran optimizer, rebuilt data with the same results)?
Jack
07 April 2017 07:36 AM
Hi AK,

Are you using QuickBooks 2017?

Did you updated your QuickBooks R5?

If you have updated QuickBooks R5, then please refer below mentioned link:

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2904
If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).