Knowledgebase: Microsoft Products
[QODBC-Desktop] QODBC query very slow in MS Access
Posted by brad waddell on 12 March 2009 05:21 PM

Troubleshooting: QODBC query very slow in MS Access

Problem Description

When trying to open a table in VB demo, it will open one with less than 1000 records, but it takes 2 to 3 minutes. The optimization does not seem to matter. When trying to open a larger table or using it with MS Access, it sits there with the icon in the taskbar saying "Waiting on QuickBooks " Record 0 of 0 and QuickBooks also freezes up or the performance is very slow.

Solutions

Supported Version and Proper Optimizer Setup When Using QODBC with MS Access

For MS Access 2003 or 2007 to work correctly you must first check that you are running QODBC v8.0.0.240 or higher and then change your optimization settings to use the "The start of every new connection (with "Load Updated Data" first) " option and confirm that the "Multi-Table Sync" option has been disabled. If not, uncheck the Multi-Table Sync option as this is often defaulted on (checked) with QuickBooks Enterprise and the QODBC Enterprise Desktop Edition.

Once this has been done, unlink all the QODBC tables in your existing MS Access .mdb(s) and then relink them to use these new connection settings.

Optimizer Relative Solution

Basically, the QODBC Optimizer mirrors the QuickBooks tables into an SQLite engine. This SQLite engine is single user only, so each user, each workstation must have its own .opt file. When QODBC is installed it will automatically optimize each table as you use it. You must allow QODBC to finish this initial optimization process. After that, updates are much faster.

Please see the link below for more about QODBC optimizer:

How to setup QODBC Optimizer and where are the Optimizer options

And re-configure the optimizer setup is also suggested. See also: How to switch OFF or RESET the QODBC Optimizer to reset the optimizer as a try.

Selecting Specified Data

Stop doing SELECT * from TableName and using MS Access datasheets instead is also suggested. Get ONLY the data you need, for example, not every invoice that ever was entered in QuickBooks, see: How to Use Prompted Date Ranges in MS Access

Optimizer File Transfer

You can copy the opt file providing these conditions are met:

1) The workstations must have the same date and time.
2) The company file must be the same company file on a networked server.
3) There are no active QODBC connections at the time.
4) The QODBC optimizer settings are the same.

(101 vote(s))
Helpful
Not helpful

Comments (1)
Bob Day
04 June 2013 11:30 PM
is this still true for QODBC POS 2013? These settings appear to re-optimize (re-load) the data EVERY TIME I make a query... with tens of thousands of records, that takes a VERY long time. Hopefully I'm missing something here :)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).