[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
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.
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 v220.127.116.11 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:
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.