Troubleshooting: QODBC query very slow in MS Access

Problem Description

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

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

The QODBC Optimizer mirrors the QuickBooks tables into an SQLite engine. This SQLite engine is single-user only. So each user and 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 the 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

Stopping SELECT * from TableName and using MS Access datasheets is also suggested. Get ONLY the data you need, for example, not every invoice 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 exact date and time.
2) The company file must be the same on a networked server.
3) There are no active QODBC connections at the time.
4) The QODBC optimizer settings are the same.