Knowledgebase
[QODBC-ALL] Using Optimizer Off for selected tables in MS-Access linked table
Posted by Jack - QODBC Support on 12 July 2017 02:35 PM

Using Optimizer Off for selected tables in MS-Access linked table

Problem Description:

I am using QODBC access QuickBooks Data. I have linked all QuickBooks tables using QODBC.

I want to use Customer table to be Optimizer OFF and rest of the tables to be Optimizer ON.

How can I achieve this?

Solution:

As you need the Customer table to be Optimizer OFF and rest of the tables to be Optimizer ON. I would suggest Using a separate DSN with Optimizer Off for the Customer table.

For this, You need to create new QODBC DSN through Configure QODBC Data Source.

You can open Configure QODBC Data Source from: Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source

Click on the "System DSN" tab to create a DSN that can be called by both Windows applications and services. Click on the "Add" button to create a new DSN.

Select the "QODBC Driver for QuickBooks" option and click on the "Finish" button.

Name your new DSN as "QuickBooks Data-OPT-OFF"

Switch to "Optimizer" tab/window and uncheck the "Use Optimizer" option & Click "Apply" followed by "OK" to save DSN configuration.

New DSN created. Close the ODBC Manager by clicking "OK".

Start QuickBooks login to Company File as Admin.

Switch to your MS-Access application. I have already linked all tables in the MS Access Database using "QuickBooks Data" DSN with Optimizer ON option.Right-click on Linked tables and select "Linked Table Manager".

From the list of tables select Customer table & enable "Always prompt for a new location" option and Click "OK".

From the DSN list select newly created DSN from "Machine Data Source", Instead of using "QuickBooks Data", Select "QuickBooks Data-OPT-OFF"

Linked table refreshed.

Customer table DSN is changed to "QuickBooks Data-OPT-OFF" from "QuickBooks Data". Close "Linked Table Manager" by clicking "Close" button.

By this way, MS Access will create a new Connection to QuickBooks Data-OPT-OFF (Optimizer Off - when accessing Customer table) and for rest of the tables, MS Access will use QuickBooks Data (Optimizer On).

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).