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 below steps

Click on the VB Demo icon to launch the Visual Basic Demo program supplied with QODBC.

From the menu bar select Connections -> Add a new connection -> select the Machine Data Source tab -> select the System DSN named QuickBooks Data -> Click OK.

 

This will connect you to the current company file opened by QuickBooks. After you click OK, if this is the first time this company file has been used by QODBC 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.

Running SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC


 

For Recurring / Scheduled Execution follow below steps

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

In the below example, we are achieving it by created 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 and saving it to e:\ and naming it as  OptimizeSelected.vbs

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

 

Below is the source of the OptimizeSelected.vbs


'*****************************************
Const adOpenStatic = 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 enter the text you see in the image into the textbox below (we use this to prevent automated submissions).