Recordset Update Problem

[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?

 

[me]

Try rs.Update just before rs.MoveNext. Also, for the added rows problem, you will need to use this Update method as well