update error

[Rizza]

I am trying to send a string to a field based on criteria in each record.
I run through once no problem. The failure is on the second time in the
loop.
If anyone can shed some light on what I am doing wrong or direction I should
head I would greatly appreciate it.

Error message:
-2147217836 (0x80040e54) The number of rows with pending changes has
exceeded the set limit.


Function subtest2()
Dim cnn As ADODB.Connection
Dim rstD As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rstD = New ADODB.Recordset
rstD.Open "[a table]", cnn, adOpenKeyset, adLockBatchOptimistic
'more code here
With rstD
Do While .EOF <> True

'more code here
!Need = "Y"
.MoveNext

'more code here
Loop
.UpdateBatch
End With
End Function

 

[Timmy]

Try this:


Function subtest2()
Dim cnn As ADODB.Connection
Dim rstD As ADODB.Recordset
Set cnn = CurrentProject.Connection
cnn.BeginTrans
Set rstD = New ADODB.Recordset
rstD.Open "[a table]", cnn, adOpenKeyset, adLockBatchOptimistic
'more code here
With rstD
Do While .EOF <> True

'more code here
!Need = "Y"
.Update
.MoveNext
'more code here
Loop
End With
cnn.CommitTrans
End Function

 

[Tim Ferguson]

am trying to send a string to a field based on criteria in each record.

strSQL = "UPDATE ATable SET Need='Y' " & _
"WHERE Something = TRUE AND SomethingElse = FALSE"
conn.Execute strSQL

Hope that helps


Tim F

 

[Rizza]

didnt work
it still fails at the second time !Need = "Y"
the same error message

 

[Rizza]

i was using an update query and am not opposed to it
the problem i was having is letting the sql know which record in the
recordset is current.

 

[Rizza]

thank you for all your help

..CursorLocation = adUseClient

this solved it after searching the KB

 

[Tim Ferguson]

i was using an update query and am not opposed to it
the problem i was having is letting the sql know which record in the
recordset is current.

In SQL there is no concept of "current record". There are just sets of
records.

Tim F