AddNew batch updating help

[Guest]

What I'm trying to do is Batch Update the database instead of update it every
time. I run a pass-through query to another database and put the results in
a recordset, then I want to dump that recordset into a table. My question is
how would I implement UpdateBatch? If I keep both rstTable.Update and
rstTable.UpdateBatch commented I still get updates? Is there any way to turn
that off so I can do just a plain batch update? The query returns 10763
results and the entire process takes about 1 minute! The pass-through query
takes quite a while, but I want to cut down update time?

Any comments? help? suggestions?
Access 2003

Private Sub cmdQuery_Click()
On Error GoTo clkErr:

Dim rstPassThrough As New ADODB.Recordset
Dim qryT As QueryDef
Dim rstTable As New ADODB.Recordset
Dim BeginTime As Date
Dim FinishTime As Date
Dim ElapsedTime As Long

BeginTime = Now

rstTable.Open "tblResult", CurrentProject.AccessConnection,
adOpenDynamic, adLockOptimistic

rstPassThrough.Open "q_TDataPhoto", CurrentProject.AccessConnection,
adOpenDynamic, adLockOptimistic

Dim iCount As Integer

'*CurrentProject.AccessConnection.Execute "DELETE * FROM tblResult"

iCount = 0
Do While rstPassThrough.EOF = False
rstTable.AddNew
rstTable("STO_RGN_ID") = rstPassThrough("STO_RGN_ID")
rstTable("AS_LST_NM") = rstPassThrough("AS_LST_NM")
rstTable("STO_OP_MKT_ID") = rstPassThrough("STO_OP_MKT_ID")
rstTable("STO_ZN_ID") = rstPassThrough("STO_ZN_ID")
rstTable("STO_ZN_DSC_TX") = rstPassThrough("STO_ZN_DSC_TX")
rstTable("STO_OP_MKT_DIR_NM_TX") =
rstPassThrough("STO_OP_MKT_DIR_NM_TX")
rstTable("STO_OP_MKT_DSC_TX") =
rstPassThrough("STO_OP_MKT_DSC_TX")
rstTable("UT_ID") = rstPassThrough("UT_ID")
rstTable("STO_NM_TX") = rstPassThrough("STO_NM_TX")
rstTable("P_CT_ID") = rstPassThrough("P_CT_ID")
rstTable("PKY_ID") = rstPassThrough("PKY_ID")
rstTable("P_CT_DSC_TX") = rstPassThrough("P_CT_DSC_TX")
rstTable("FCL_YR_ID") = rstPassThrough("FCL_YR_ID")
rstTable("FCL_YR_ID0") = rstPassThrough("FCL_YR_ID0")
rstTable("FCL_PER_ID") = rstPassThrough("FCL_PER_ID")
rstTable("WK_END_DT_2") = rstPassThrough("WK_END_DT_2")
rstTable("RET_DOLLARS") = rstPassThrough("RET_DOLLARS")
rstTable("SALES_UNITS") = rstPassThrough("SALES_UNITS")
rstTable("DM_DOLLARS") = rstPassThrough("DM_DOLLARS")
rstTable("WJXBFS1") = rstPassThrough("WJXBFS1")
rstTable("END_STO_INV_QT") = rstPassThrough("END_STO_INV_QT")
rstTable("END_STO_INV_COST") = rstPassThrough("END_STO_INV_COST")
rstTable("WJXBFS2") = rstPassThrough("WJXBFS2")
rstTable("WJXBFS3") = rstPassThrough("WJXBFS3")
rstTable("WJXBFS4") = rstPassThrough("WJXBFS4")

rstPassThrough.MoveNext
'*rstTable.Update

Loop
'*rstTable.UpdateBatch




rstPassThrough.Close
' rstTable.Close

FinishTime = Now

ElapsedTime = DateDiff("s", BeginTime, FinishTime)

MsgBox ElapsedTime & " : Seconds"

clkExit:
Exit Sub
clkErr:
MsgBox Err.Number & " , " & Err.Description
Resume clkExit

End Sub

 

[Stefan Hoffmann]

hi David,

The query returns 10763
results and the entire process takes about 1 minute! The pass-through query
takes quite a while, but I want to cut down update time?

Do While rstPassThrough.EOF = False
rstTable.AddNew
rstTable("STO_RGN_ID") = rstPassThrough("STO_RGN_ID")
rstTable("AS_LST_NM") = rstPassThrough("AS_LST_NM")
rstPassThrough.MoveNext
Loop

Use a plain SQL statement:

CurrentDb.Execute "INSERT INTO tblResult (FieldList) " & _
"SELECT Fieldlist FROM q_TDataPhoto"


mfG
--> stefan <--

 

[Guest]

Thanks!

I changed it to a
CurrentProject.AccessConnection.Execute "q_TDataPhoto"
CurrentDb.Execute "DROP TABLE tblResult;"
CurrentDb.Execute "q_MakeTblResult"

where I run the query then I remake my result table off of it with a SELECT
fieldlist INTO tblResult FROM q_TdataPhoto