Knowledgebase
[QODBC-Desktop] Getting 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 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 in 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 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. I would like to suggest you to 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
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

(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 enter the text you see in the image into the textbox below (we use this to prevent automated submissions).