Knowledgebase
[QODBC-ALL] How to execute SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC for Selected Tables
Posted by Rajendra Dewani (QODBC Support) on 05 October 2012 03:20 PM

There are multiple ways to execute SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC for selected/multiple tables.

For a Single Execution, follow the below steps.

Click on "Start" and "Programs" and locate QODBC Driver for the QuickBooks program group. Click on the QODBC Test Tool icon to launch the program supplied with QODBC.

 

 

To establish a connection, select "QuickBooks Data" -> DSN from the dropdown list and click on "Connect."

 

This will connect you to the current company file opened by QuickBooks. After you click Connect, if this is the first time QODBC has used this company file, you will receive a popup from QuickBooks asking you to allow QODBC to run. Choose the Yes, Always option, and check the Sensitive data option.

Once you are connected, type SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC and Click on "Run."

 

After execution, the following message will appear: "Total Number of Records 0."

 

For Recurring / Scheduled Execution, follow the below steps.

( ONLY IF YOU WOULD LIKE TO SCHEDULE OR CREATE A BAT TO RUN OPTIMIZER FOR SELECTED/MULTIPLE TABLES QUICKLY)

In the below example, we are achieving it by creating a VBScript.

You can create a script/exe/VBA code and execute SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC for a select table.

In this example, we are creating a VBScript, saving it to e:\, and naming it OptimizeSelected.VBS

( you may change the name and drive location of your choice, please apply the same modification to the execution statement)

 

Below is the source of the OptimizeSelected.VBS


'*****************************************
Const adopt static = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open sConnectString
sSQL = "SP_OPTIMIZEUPDATESYNC Invoice"
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic

sSQL = "SP_OPTIMIZEUPDATESYNC Customer"
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic

sSQL = "SP_OPTIMIZEUPDATESYNC Bill"
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic

Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing

'*****************************************

 

How to execute

 

For 64 bit Machine you can execute this from below command

C:\Windows\SysWOW64\cmd.exe /c cscript e:\OptimizeSelected.vbs

 

For 32 bit Machine you can execute this from below command

C:\Windows\System32\cmd.exe /c cscript e:\OptimizeSelected.vbs

 

Also, Refer

VB Demo Missing

TroubleShooting: Cannot Get Full Records From QuickBooks

How to switch OFF or RESET the QODBC Optimizer

(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).