Update ID field of all the records

[aman]

Hi guys

I have a table which has around 60,000 records but the table doesn't have any ID field. Now I have created ID field in the table but it has no data. I am looking for VBA code that will update ID of all the records all together and ID should start with 15,000.

Can anyone please halp me to write vba code for this?

Thanks

 

[Addyman]

Something like:

Dim rs as recordset, lngCounter as long, strSQL as string

lngCounter = 15000

strSQL = "SELECT ID FROM tbl_YourTableName"

Set rs = CurrentDb.OpenRecordset(strSQL)

rs.MoveFirst

Do Until rs.EOF

With rs

.Edit
rs![ID] = lngCounter
.Update

End With

lngCounter = lngCounter + 1

rs.MoveNext

Loop

Please note - not tested this but should be somewhere close

 

[Mr. B]

A record ID field is normally defined as an AutoNumber type field.

You did not indicate how you have defined your "ID" field. If you will make this field to be and AutoNumber type field it will be automatically populated and no code will be required to populate it.

 

[Addyman]

A record ID field is normally defined as an AutoNumber type field.

You did not indicate how you have defined your "ID" field. If you will make this field to be and AutoNumber type field it will be automatically populated and no code will be required to populate it.

True but this would start at 1, not 15,000. You can change the start number but it requires some fiddling about (easier if the table is in SQL then you can specify the start number).

 

[Mr. B]

A record ID field should be just that, a unique record identification field. This is why the AutoNumber field is used. This field should never be used for anything except the identification of one single record.

If the OP needs a number starting at 15,000 and then being incrementing by one for each subsequent record, then the method you provided appears to do that job. However, that type of field should not be used as the record ID. The incrementing number field will have to be manually updated through the use of VBA code or a macro.