P
PeteCresswell
I'm appending about 2 bazillion (actually 131,760) records from a work
table to a back end table via an Append query.
Query runs ok when executed manually, although it pops "There isn't
enough disk space or memory to undo the data changes this action query
is about to make." and takes quite a long time to run.
But if I invoke it in VBA and wrap the process in a transaction, the
code throws "3035: System resource exceeded."
Bracketing it in DoCmd.SetWarnings = True/False doesn't help.
OTOH, not wrapping it in a transaction makes the error message go
away.
I'm guessing that:
-------------------------------------------------------------------------------------------------
1) Running a single append query is inherantly wrapped in a
transaction and my transaction wasn't needed anyhow.
2) The error was something to do with available memory vs number of
records.
3) As long as it works without the transaction, I'm home free
-------------------------------------------------------------------------------------------------
Anybody disagree?
The code:
==========================================================
'
------------------------------------------------------------------
' Open up a transaction and append work table contents to prod
table
14910 Set thisWS = DBEngine(0)
14911 thisWS.BeginTrans
14919 transOpen = True
14920 Set myQuery = CurrentDb.QueryDefs
("qryPaymentAccruals_Update_Bulk11_CopyWorkToProd")
14921 With myQuery
14922 .Parameters("theUserID") = myUserID
14923 .Parameters("theTimeStamp") = myTimeStamp
14994 .Execute dbFailOnError
14929 End With
14990 thisWS.CommitTrans
14991 transOpen = False
==========================================================
table to a back end table via an Append query.
Query runs ok when executed manually, although it pops "There isn't
enough disk space or memory to undo the data changes this action query
is about to make." and takes quite a long time to run.
But if I invoke it in VBA and wrap the process in a transaction, the
code throws "3035: System resource exceeded."
Bracketing it in DoCmd.SetWarnings = True/False doesn't help.
OTOH, not wrapping it in a transaction makes the error message go
away.
I'm guessing that:
-------------------------------------------------------------------------------------------------
1) Running a single append query is inherantly wrapped in a
transaction and my transaction wasn't needed anyhow.
2) The error was something to do with available memory vs number of
records.
3) As long as it works without the transaction, I'm home free
-------------------------------------------------------------------------------------------------
Anybody disagree?
The code:
==========================================================
'
------------------------------------------------------------------
' Open up a transaction and append work table contents to prod
table
14910 Set thisWS = DBEngine(0)
14911 thisWS.BeginTrans
14919 transOpen = True
14920 Set myQuery = CurrentDb.QueryDefs
("qryPaymentAccruals_Update_Bulk11_CopyWorkToProd")
14921 With myQuery
14922 .Parameters("theUserID") = myUserID
14923 .Parameters("theTimeStamp") = myTimeStamp
14994 .Execute dbFailOnError
14929 End With
14990 thisWS.CommitTrans
14991 transOpen = False
==========================================================