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

Problem Description:

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

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

How can I achieve this?

Solution:

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

You need to create a a 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 both Windows applications and services can call. 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 the "Optimizer" tab/window and uncheck the "Use Optimizer" option & Click "Apply" followed by "OK" to save the DSN configuration.

Start QuickBooks login to Company File as Admin. Start QuickBooks login to Company File as Admin. Start QuickBooks login to Company File as Admin. New DSN created. Close the ODBC Manager by clicking "OK."

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

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

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

The linked table has been refreshed.

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

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