Knowledgebase
[QODBC-Desktop] Using Optimizer Off for selected tables in MS-Access linked table with QRemote
Posted by Jack - QODBC Support on 04 December 2017 07:16 AM

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

Problem Description:

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

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

How can I achieve this?

Solution:

As you need the Customer table with Optimizer OFF and rest of the tables to be Optimizer ON. You need to use 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".

After this, You need to create new QRemote DSN through Configure QODBC Data Source 64-bit.

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

Note: If you are using 32-bit application & using QRemote, then you need to open Configure QODBC Data Source from: Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source & create new QRemote DSN from 32-bit ODBC Manager.

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 "QRemote for QuickBooks" option and click on the "Finish" button.

Name your new DSN as "QRemote-OPT-OFF"

·         Server: IP Address displayed on QRemote Server 

·         Port: Port displayed on QRemote Server 

·         Encryption Key/Password: (By Default Encryption Key/Password is set to "QODBC#QRemote$1". In case you want to change Encryption Key/Password, You can configure your encryption key on QRemoteServer and use the same on QRemote Client DSN) 

·         Encryption(By Default Encryption is set to Off. In case you want to use encrypted data transfer between QRemote client and QRemoteServer, You can configure encryption on QRemoteServer and use the same on QRemote Client DSN) 

·         Remote DSN: select QuickBooks Data-OPT-OFF DSN from the list. (Make sure QRemote Server is running)  

Click "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 64-bit QRemote", Select "QRemote-OPT-OFF"

Linked table refreshed.

Customer table DSN is changed to "QRemote-OPT-OFF" from "QuickBooks Data 64-bit QRemote". 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).