Creating a column of consecutive numbers

[bluemat]

I have a large database with 140, 000 records in it. I would like to give each one a consecutive ID, so I would like to add a new column with consecutive numbers 1 - 140,000

help?

thanks

 

[RichUK]

Why not just use an Autonumber?

 

[Neem]

I would have thought that autonumber is the obvious answer...! Is there a reason why you havent tryed this?

 

[bluemat]

I have tried but the database is too big to do al records. And I cannot manually update certain records and auto number the rest....

 

[GGleason]

Try this. You need a valid SQL statement that allows updates to your table (updateable recordset SQL where the red text is) and the name of the field (where the green text is):
[tt]
strSQL = “SELECT ….”
Set dbs = OpenDatabase(CurrentDb.Name)
Set qdf = dbs.CreateQueryDef("", strSQL)
Set rst= qdf.OpenRecordset(dbOpenDynaset)

If rst.BOF = False Then
rst.MoveLast
lngMaxRec = rst.RecordCount
rst.MoveFirst
Else
lngMaxRec = 0
End If

For i = 1 To lngMaxRec


With rst
.Edit
!RecRef = i
.Update
.Bookmark = .LastModified
End With
'-------------------
rst.MoveNext
Next i

rst.close
qdf.close
dbs.close
set rst = Nothing
set qdf = Nothing
set dbs = Nothing

Beep
Msgbox “End of update”
[/tt] [/tt] [/tt]

hth,
GGleason

 

[bluemat]

Thanks!