On Jun 24, 8:57*am, Roger <lesperan... (AT) natpro (DOT) com> wrote:
I'm using a ADODB.Connection to wrap a complex update into a
transaction
where I do many selects, inserts and updates, processing many detail
lines

I do this so that it can all be rolled back if something fails

the transaction process usually works, except this morning
we can across a situation where it seemed to handcuff sqlServer
where the 'UPDATE' statement (which is done once per line)
was blocking the .addNew for the next line

is there another way to implement transactions using access2007 ?

this is a strip down version of the transaction
error trapping removed

Public cnn As ADODB.Connection
Set cnn = Application.CurrentProject.Connection
cnn.CursorLocation = adUseClient
cnn.CommandTimeout = 600
cnn.BeginTrans

Set rs2 = New ADODB.Recordset
rs2.Open "SELECT * FROM tblSLS where 1 = 2", cnn, adOpenDynamic,
adLockOptimistic

strSql = "SELECT *" & _
" FROM qryCreateSls" & _
" WHERE partsQuoteId = " & CLng(partsQuoteId)
Set rs = New ADODB.Recordset
rs.Open strSql, cnn
With rs
rs2.AddNew
...
end with

more stuff

strSql = "UPDATE tblPartsQuoteDetail" & _
" SET onHandoverId = " & lngHandoverId & _
" , onLineNum = " & rs3!lineNum & _
" WHERE partsQuoteId = " & rs3!partsQuoteId & _
" AND lineNum = " & rs3!lineNum
cnn.Execute strSql, , dbFailOnError

more stuff
cnn.commitTrans