Create a new field or column Using DAO

If you want to add the new field or column in the table.

Here is the code-


Sub add_new_field_using_DAO()
Dim fld As DAO.Field
Dim postion As Integer, i As Integer
postion = 0
On Error Resume Next
' CLOSE THE TABLE IF ITS OPEN
DoCmd.Close acTable, "SALES_DETAIL", acSaveYes

' ASSIGNING ORDINALS TO FIELDS
For i = 0 To CurrentDb.TableDefs("sales_detail").Fields.Count - 1
CurrentDb.TableDefs("sales_detail").Fields(i).OrdinalPosition = i
Next
' ordinalposition is used to allocate the positions to the fields like 1 position in table or 2nd ,etc

' Increasing each ordinal by 1 and we will have 0 as free and the new field which we will add can be allocated to zero position that means 1st position in table.

For J = CurrentDb.TableDefs("sales_detail").Fields.Count - 1 To 0 Step -1
CurrentDb.TableDefs("sales_detail").Fields(J).OrdinalPosition = J + 1
Next J
' adding new field AT FIRST POSITION IN THE TABLE
Set fld = CurrentDb.TableDefs("sales_detail").CreateField("S_No", dbLong)
fld.OrdinalPosition = postion
CurrentDb.TableDefs("sales_detail").Fields.Append fld
CurrentDb.TableDefs("sales_detail").Fields.Refresh
End Sub


Download Access Database