Batch Update - using VBA - need help is this code correct?

[humour]

The code below works just fine....(simplified for clarity)

BUT...

I am not an expert on LockTypes or the Update options I have when parsing through a record set. Is the code below the most efficient? Specifically am I using the right lock mechanism. There will only be one user updating records when this code is run. Data contention is not an issue here.



rst.ActiveConnection = CurrentProject.Connection
SqlStr = "Select po_po_no, po_rec_no, po_reqd_date from purchasedetailstable "

rst.LockType = adLockBatchOptimistic
rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic
rst.Open (SqlStr)

Do Until rst.EOF
rst.Fields.Item("desc").Value = "zzz"
rst.MoveNext
rst.UpdateBatch
Loop

 

[SiJP]

When only one user is updating the data, LockType is best set to adLockBatchOptimistic as it will only lock records when the .UpdateBatch triggers. This is according to MSDN library anyhow

</2cents>



------------------------
Hit any User to continue