ms access - Record edited twice if edited column has index -

ms access - Record edited twice if edited column has index -

tablea has 1 column 'fielda' of type long.

there 3 records in table values 3,4 , 5 respectively.

after running code below values should 18, 19 , 20.

this case if there isn't index on fielda if there vaues 33, 19 , 20.

one record gets edited twice. bug in dao or normal behaviour?

dim rs recordset dim s1 string s1 = "select * tablea" set db = opendatabase(dbaddress) set rs = mydb.openrecordset(s1) if not rs.bof or not rs.eof rs.movefirst while not rs.eof rs.edit rs.fields("fielda").value = rs.fields("fielda").value + 15 rs.update rs.movenext loop end if

while unable recreate behaviour describe can offer 1 possible explanation. stepping through records may nail same record more 1 time if recordset periodically checks changes may have been made underlying table other users.

say recordset starts out as

3   4   5

and update first record table contains

18   4   5

if recordset tries "refresh" , index on [fielda] controls order in records appear in recordset end being

3   4   5   18

and if continues updating until .eof final result be

3   19   20   33

possible workarounds to

create recordset sql statement includes order clause on some other field order of records not alter modify them, or

apply update via sql, e.g. update tablea set fielda = fielda + 15

ms-access dao