Problem updating changes to table

  • Thread starter Thread starter al russcol
  • Start date Start date
A

al russcol

Hi all,

Using Win2k and Office2k, I am making changes to a field
in a recordset and the VBA code works perfectly but after
stepping through about six fields I get the error msg:

"Number of rows with pending changes exceeded the limit."

The changes are taking place properly when checking the
immediate window and moving the cursor over the field
name. I watch the code execute:

rs.update

but the changes are never reflected in the table.
I'll bet this is very simple but I don't see the problem.

Thanks for any insight,

al

PS I tried checking the update syntax but even after
reloading Office the help will find the update method but
will not display the subject. I tried four different
machines and all have the same problem.
 
This is everything but the stock MS error handling:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "select * from mstrtst",
CurrentProject.Connection, adOpenDynamic,
adLockBatchOptimistic

Dim x As Integer
Dim FieldLen As Integer

rs.MoveFirst
Do Until rs.EOF

FieldLen = Len(rs![PhnStripped])
x = 1
Do Until x > FieldLen

If Mid(rs![PhnStripped], x, 1) > Chr(57) Or Mid(rs!
[PhnStripped], x, 1) < Chr(48) Then

If FieldLen = 1 Then
rs![PhnStripped] = ""
Else

rs![PhnStripped] = Left(rs![PhnStripped],
x - 1) & Mid(rs![PhnStripped], x + 1, FieldLen)

End If

rs.Update
x = x - 1
FieldLen = FieldLen - 1

End If
x = x + 1

Loop

rs.MoveNext
Loop

Thanks,

al
 
I found it! Somehow I got an adlockBATCH which prevented
the update from working

thanks,

alr

-----Original Message-----

This is everything but the stock MS error handling:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "select * from mstrtst",
CurrentProject.Connection, adOpenDynamic,
adLockBatchOptimistic

Dim x As Integer
Dim FieldLen As Integer

rs.MoveFirst
Do Until rs.EOF

FieldLen = Len(rs![PhnStripped])
x = 1
Do Until x > FieldLen

If Mid(rs![PhnStripped], x, 1) > Chr(57) Or Mid (rs!
[PhnStripped], x, 1) < Chr(48) Then

If FieldLen = 1 Then
rs![PhnStripped] = ""
Else

rs![PhnStripped] = Left(rs![PhnStripped],
x - 1) & Mid(rs![PhnStripped], x + 1, FieldLen)

End If

rs.Update
x = x - 1
FieldLen = FieldLen - 1

End If
x = x + 1

Loop

rs.MoveNext
Loop

Thanks,

al





-----Original Message-----

I don't know how You trying to update your data.
Show me a code.


.
Click to expand...
.
Click to expand...
 
Back
Top