Trying to do Incremental run in Access 1

[miller1975]

Hello All,
I have a Access SQL query that I am running from VB, but for some reason it doesnt come right from VB but the query works great from Access. Now I can run it from Access but the problem is the query fetches over 3.5 million records.
So I had set a incremental run code in VB.
Can I do a similar setup in Access.

This is my query in VB...

Dim max, incr, start, i, iter, strSql
    On Error GoTo Err_Handler
    txtStream.WriteLine "CONTRACT_THOR Started at " & CStr(Now)
    max = 5000000
    incr = 20000
    start = 0
    i = start
    iter = 0
    
    While i < max
        strSql = "INSERT INTO CONTRACT_THOR ( CUSTOMER_NBR, CONTRACT_ID, 
BRANCH_CO_NBR, BRANCH_NBR, BRANCH_NAME, DEALER_NBR, DEALER_NM, SOG_NBR, 
SOG_NAME, CNTR_BILL_ADDR, BILLTO_NAME, ACCT_PLAN_ID, SETUP_DATE, 
CONT_COLL_STAT, EMPLOYEE_WORKING, NBR_DAYS_PST_DUE, CYCLE_DAY, 
CONTRACT_BAL, TOT_CONTRACT_BAL, RENT_NOW_DUE, RENT_CURR_AGED, 
RENT_OV30_AGED, RENT_OV60_AGED, RENT_OV90_AGED, RENT_OV120_AGED, 
RENT_OV150_AGED, RENT_OV180_AGED, TEAM, CUSTOMER_NAME )"
        strSql = strSql + " SELECT A.CUSTOMER_NBR, A.CONTRACT_ID, 
A.BRANCH_CO_NBR, A.BRANCH_NBR, C.BRANCH_NAME, D.DEALER_NBR, D.DEALER_NM, 
D.SOG_NBR, E.SOG_NAME, A.CNTR_BILL_ADDR, NZ(I.LAST_NAME,'?') AS 
BILLTO_NAME, A.ACCT_PLAN_ID, A.SETUP_DATE, F.CONT_COLL_STAT, 
NZ(J.PSYS_EMPLOYEE_ID,'????????') AS EMPLOYEE_WORKING, F.RT_DAYS_PST_DUE, 
A.CYCLE_DAY, A.TOTAL_RENT AS CONTRACT_BAL, A.TOTAL_RENT+B.TOT_NOW_DUE AS 
TOT_CONTRACT_BAL, B.TOT_NOW_DUE AS RENT_NOW_DUE, B.TOT_CURRENT_AGED AS RENT_CURR_AGED, B.TOT_OVER_30_AGED AS RENT_OV30_AGED, B.TOT_OVER_60_AGED AS 
RENT_OV60_AGED, B.TOT_OVER_90_AGED AS RENT_OV90_AGED, B.TOT_OVER_120_AGD AS 
RENT_OV120_AGED, B.TOT_150_AGED_AMT AS RENT_OV150_AGED, 
B.TOT_210_AGED_AMT+B.TOT_240_AGED_AMT+B.TOT_270_AGED_AMT+B.TOT_300_AGED
_AMT+B.TOT_330_AGED_AMT+B.TOT_360_AGED_AMT+B.TOT_180_AGED_AMT 
AS RENT_OV180_AGED, NZ(J.PSYS_TEAM_ID,'????') AS TEAM, NZ(H.LEGAL_NAME,' ') 
AS CUSTOMER_NAME"
        strSql = strSql + " FROM (((((((ACRM1_CONTRACT AS A INNER JOIN 
ACRM1_SCHEDULE_TOTALS AS B ON (A.CUSTOMER_NBR=B.CUSTOMER_NBR) AND 
(A.CONTRACT_ID=B.CONTRACT_ID)) INNER JOIN ACRM1_BRANCH AS C ON 
(A.BRANCH_CO_NBR=C.BRANCH_CO_NBR) AND (A.BRANCH_NBR=C.BRANCH_NBR)) INNER 
JOIN ACRM1_DEALER AS D ON (C.DEALER_CO_NBR=D.DEALER_CO_NBR) AND 
(C.DEALER_NBR=D.DEALER_NBR)) INNER JOIN ACRM1_SOG AS E ON 
(D.SOG_CO_NBR=E.SOG_CO_NBR) AND (D.SOG_NBR=E.SOG_NBR)) INNER JOIN 
ACRM1_CONTRACT_II AS F ON (A.CUSTOMER_NBR=F.CUSTOMER_NBR) AND 
(A.CONTRACT_ID=F.CONTRACT_ID)) INNER JOIN ACRM1_CUSTOMER AS H ON 
A.CUSTOMER_NBR=H.CUSTOMER_NBR) INNER JOIN ACRM1_CONTRACT_NAMES AS I ON 
(A.CUSTOMER_NBR=I.CUSTOMER_NBR) AND (A.CONTRACT_ID=I.CONTRACT_ID)) LEFT 
JOIN ACRM1_CUST_CONT_TEAM AS J ON (J.CUSTOMER_NBR=A.CUSTOMER_NBR) AND 
(J.CONTRACT_ID=A.CONTRACT_ID)"
        strSql = strSql + " WHERE (A.CONTRACT_STATUS='A' Or (A.CONTRACT_STATUS='T' And A.CLOSE_BILL_IND<>'T')) And A.ACCT_PLAN_ID Not 
In ('OPR','OPRC') And B.SCHEDULE='B' And B.SCHEDULE_TYPE='RENT' And 
I.NAME_TYPE='BLTO')"
        strSql = strSql + " And ((A.CUSTOMER_NBR)>=" & CStr(i) & " And (A.CUSTOMER_NBR)<" & CStr(i + incr) & "))"
        adoConn.Execute strSql
        txtStream.WriteLine CStr(i + incr) & " completed " & CStr(Now)
        i = i + incr
        iter = 0
    Wend

Want do the same in Access

 

[fredericofonseca]

As you have been told before Access will not hold that type of volume very well.

You really should think in changing the backend to SQL Server or at least to MSDE engine.



If you wish to be sure the SQL code is exactly the same as on Access you should use a parameterized qry, and eventually use a command object to supply the customer interval.

In this case it is also possible that your problem is the timeout interval on the connection object.

If you wish to run this with intervals on Access you can also use a parameterized query and use a macro to populate the parameters, or you can use a similar bit of code on a module and execute the SQL through the docmd method.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[miller1975]

Hello,
Can you elobrate on this solution you have mentioned

If you wish to run this with intervals on Access you can also use a parameterized query and use a macro to populate the parameters, or you can use a similar bit of code on a module and execute the SQL through the docmd method.

And yes its the timeout issue (there are 2 networks, 2 firewalls involved in this process) thats causing the process to fail, if I dont use the incremental paramete.

 

[fredericofonseca]

Change

strSql = strSql + " WHERE (A.CONTRACT_STATUS='A' Or (A.CONTRACT_STATUS='T' And A.CLOSE_BILL_IND<>'T')) And A.ACCT_PLAN_ID Not In ('OPR','OPRC') And B.SCHEDULE='B' And B.SCHEDULE_TYPE='RENT' And I.NAME_TYPE='BLTO')"
strSql = strSql + " And ((A.CUSTOMER_NBR)>=" & CStr(i) & " And (A.CUSTOMER_NBR)<" & CStr(i + incr) & "))"

to

strSql = strSql + " WHERE (A.CONTRACT_STATUS='A' Or (A.CONTRACT_STATUS='T' And A.CLOSE_BILL_IND<>'T')) And A.ACCT_PLAN_ID Not In ('OPR','OPRC') And B.SCHEDULE='B' And B.SCHEDULE_TYPE='RENT' And I.NAME_TYPE='BLTO'"
strSql = strSql + " And ((A.CUSTOMER_NBR)>=" & CStr(i) & " And (A.CUSTOMER_NBR)<" & CStr(i + incr) & ")"

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com