Updating Recordset with numbers

[Anne]

I have a table with over 36,000 records and I want to number each record in
numerical order (i.e. 1, 2, 3, 4, 5...etc). I'm using the following code to
update each record. I don't want to use autonumber because this process is
run daily and I want it to always start with the #1, if I purge the table of
prior day's data the numbering does not start at 1.

The code is:

i = 1
Do
rstMedian.Edit
rstMedian![Fld_Rank] = i
rstMedian.Update
i = 1 + i
rstMedian.MoveNext
Loop Until rstMedian.EOF

The problem is after it updates a certain number of records (it is appearing
to be either the 9493 or 9494 record) it doesn't update the Rank for either
the 9493 or 9494 record, then it continues on till the next 9493 or 9493
record which is 18988 or 18986, then it skips that and then continues until
the next 9493 or 9494 record then skips that then continues on.

Why won't it update those records? Since there is some logic to why it
isn't being updated (every 9493 or 9494 record) I don't understand why it
would do that. Any thoughts would be appreciated. Thanks.

 

[Allen Browne]

I don't see any reason why it should skip records in the position of
multiples of 9493 or 4. There could possibly be locking issues, errors that
are being suppressed, a corrupt index, or the order of records is not as you
suspect.

In any case, an easier solution might be to use an AutoNumber, and reset it
after each delete. There's an example of how to do that here:
http://allenbrowne.com/func-ADOX.html#ResetSeed