Knowledgebase
[QODBC-ALL] Troubleshooting: Slow Performance / Cannot Get Full Records from QuickBooks / How to find missing data
Posted by Rajendra Dewani (QODBC Support) on 16 October 2012 11:13 AM

Troubleshooting: Slow Performance / Cannot Get Full Records from QuickBooks / How to find missing data

Problem Description 1:

1.QODBC is showing Partial data or few of my data is missing in the result set.

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.
 

Problem Description 2:

We have a custom report that summarizes custom data fields in QuickBooks. When we updated from QB ES 15 to 16, the report stopped working stating that we needed to update the QODBC driver version. We installed the version within QuickBooks, however, the report would return 'blank' reports using a list of 30-40 different parameters. An investigation by a Crystal developer revealed that the report stops returning ANY data at a certain number of parameters passed, but works as designed up to that point. We didn't see this limitation prior to the upgrade from 15 to 16. The report is used to predict and budget revenue. 

Problem Description 3:

Every two weeks, we use an access database to read employee paycheque details from QuickBooks.

The last two times, the first time we ran the query, we were missing exactly one record.

Both times, simply rerunning the query gave us the full expected recordset.

We did not even close and re-open the database or QuickBooks between each running of the query.

What could be causing this? 

Problem Description 4:

Last Friday we ran updates on QuickBooks 2015. For some reason, our MS Access application stops getting results from queries. I noticed VB Demo still works.

I even tried a simple query and just hangs with the circle 

Problem Description 5:

When I filter the QODBC Transaction Table to only show Income, Expense, Other Income and Other Expense and only transactions in our current Fiscal Year, it does not match the QB generated P&L Report for the same time frame. It seems like random sections of certain journal entries is missing from QODBC. I tried "Rebuilding" the QB Company File, but it did not help. Any ideas?

 

Solution:

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 

Check the Use Optimizer option to reactivate and reset the Optimizer.

See also: How do I setup the QODBC Optimizer? Where are the Optimizer options? 

In Case, your Optimizer file is Corrupted you can delete the Optimizer file and QODBC will generate a new one in the next data connect.

For QODBC 13.0.0.292 and Above, you can also Reset the Optimizer file on a Single Click from
QODBC Setup Screen->Optimizer Window->Reset Optimizer File(s)

 

 

Also Refer: How to Remove Optimizer file

If above steps do not work, Please follow below Manual Steps for resetting Optimizer

1) Close all applications other than QuickBooks/Reckon.

2) Find the file with .opt extension at the path you had assigned to "QODBC optimizer" and delete the optimizer file.
(The default path is %AppData%\QODBC Driver for QuickBooks\Optimizer.
Here are the steps to locate the folder
Click on Start Button,
Click on Run,
Type the below command & press enter
%appdata%\QODBC Driver for QuickBooks\Optimizer
This should Open a folder)

And also, please refer to How to switch OFF or RESET the QODBC Optimizer for more information about resetting QODBC optimizer. 

3. Build Optimizer file

Please run below command in VB Demo: 

sp_optimizefullsync ALL
 


Or

sp_optimizefullsync SalesOrder
sp_optimizefullsync Invoice

 

Please Note: You need to optimize parent table only. There is no need to optimize child table separately.


For Example:

When you optimize "Invoice" table, QODBC will automatically optimize the child table "InvoiceLine", i.e. Invoice & InvoiceLine will be optimized.

When you optimize "Bill" table, QODBC will automatically optimize the child tables "BillItemLine' & "BillExpenseLine", i.e. Bill, BillItemLine & BillExpenseLine will be optimized.

Please refer: How to execute SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC for Selected Tables


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.
 
Please Note: Optimizer file is for each user and cannot be shared.

4. Backup Optimizer File.
 
Please take backup of Optimizer file in your backup folder after Optimizer file build up. This might help you if optimizer file gets corrupted, You can save the number of hours for building new optimizer file if you have this file. You can restore the backup in case of Optimizer corruption. You need to remove corrupted .opt file &  place Optimizer backup file in your Optimizer path (the default path is %AppData%\QODBC Driver for QuickBooks\Optimizer). After restoring the .opt file you need to run sp_optimizeupdatesync ALL for getting delta changes. This command will synchronize the all table data with the QuickBooks data file using changed and deleted data. It is useful for making sure that the optimizer is up to date with the QuickBooks company file.  

5. Close VB Demo. 

6. Enable QODBC Status Panel:

Please also enable QODBC status panel via QODBC Setup Screen->Message Window->Select "Display Driver Status" and "Display optimizer Status" options.

 



And then the next time you run a query, if you see “Waiting for QuickBooks”, it means QuickBooks is taking the time to process the request. There will be a status panel at the lower right corner of your screen, will be shown a window with information on what QODBC is working at. Please note the step on which QODBC spends most time or get stuck. 

7. Start your application again & test again. 

Additional Information:

Test Without QODBC optimizer

First, please turn off your QODBC optimizer (QODBC Setup Screen->Optimizer Window->Uncheck "Use Optimizer" option), then use your query in VB Demo to test again to see if it can bring back those missing records. And if it works, then please follow the below steps

 Or

To temporarily bypass your optimizer settings and extract the latest information you can add the unoptimized tag after the table name like this to your queries:

    select * from InvoiceLine unoptimized where TxnDate >= {d'2006-03-06'}.


If Above Solutions doesn't work, please post a ticket to us and we will do a further investigation on your issue.

Also, Refer:
Troubleshooting - Cannot load Customer table records

 

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 (0)
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).