Create a new Field or Column in Access Table and then add data to it

If you want to add a new field or column in a table and then add data to that column.


Here is the code-


Sub create_a_new_field_in_table_add_serial_numbers()
' we will add a new field in the table salesdetails with S.no and will give numbers like 1, 2,3 ,4 etc.
Dim rs As Recordset
Dim tdf As TableDef, fld As DAO.Field
Dim counter As Long
counter = 1
DoCmd.SetWarnings (False)
DoCmd.Close acTable, "SALES_DETAIL", acSaveYes

On Error Resume Next
' CLOSE THE TABLE IF ITS OPEN
DoCmd.Close acTable, "SALES_DETAIL", acSaveYes
' delete if S_NO is previously created
DoCmd.RunSQL "ALTER tABLE SALES_DETAIL DROP S_NO long"
' create new field
DoCmd.RunSQL "ALTER tABLE SALES_DETAIL ADD S_NO long"


' adding numbers like 1 , 2 ,3 , 4,5 , etc
Set rs = CurrentDb.OpenRecordset("SALES_DETAIL", dbOpenDynaset)
Do While Not rs.EOF
rs.Edit
rs!S_NO = counter
rs.Update
counter = counter + 1
rs.MoveNext
Loop
rs.Close
DoCmd.SetWarnings (True)
End Sub


Download Access Database