Recordset Update Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a block of code that looks something like this

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
con.ConnectionString = "DSN=......"
con.Open
sql = " SQL Statement "
rs.ActiveConnection = con
rs.CursorType = adOpenStatic
rs.Open sql

DoCmd.SetWarnings False
Do while not rs.EOF
update table set name = '' where something = ''
rs.movenext
loop

DoCmd.SetWarnings True
con.close

This block is code is triggered when a button is clicked.
What I can't understand is that even though going through
this loop updates the data in a particular table and when
I open the table I can see those changes, the recordset
(rs) continues to show the data in its original form
before the updates. For instance if I said in the code to
update field1 with "Bill" where field1 = "William" ,
field1 would be updated and when I open the table I will
see "William" but the next time the code runs if I insert
a breakpoint to see the value of the recordset field I
will see "Bill". This is causing a problem in that the
recordset for some reason is not recognizing changes to
the database. There were a set of new rows that were
added that the recordset totally ignores like they are not
there. I don't know what to do to correct this. Can
someone help me out?
 
Try rs.Update just before rs.MoveNext. Also, for the added rows problem, you will need to use this Update method as well
 
Back
Top