update error

  • Thread starter Thread starter Rizza
  • Start date Start date
R

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
 
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
 
am trying to send a string to a field based on criteria in each record.
Click to expand...

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

Hope that helps


Tim F
 
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.
 
thank you for all your help

..CursorLocation = adUseClient

this solved it after searching the KB
 
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.
Click to expand...

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

Tim F
 
Back
Top