Firstly, you need to create Database and Recordset object, make sure the DAO access object library is enabled/added to the project.
Create the UpdateTableField Sub
Sub UpdateTableField(tb As String, new_fld As String)Dim CHECK As Boolean
CHECK = False
Dim tbl As TableDef
Dim fld As Field
Dim strName As String
Set tbl = DB.TableDefs(tb)
For Each fld In tbl.Fields
If fld.Name = new_fld Then
CHECK = True
End If
Next
If CHECK = False Then
Set RS = Nothing
With tbl
Set fld = .CreateField(new_fld, dbText)
fld.DefaultValue = “”
.Fields.Append fld
MsgBox “new Field Created !, check table”
End With
Else
MsgBox “Field already exist !”End If
The ‘TableDefs’ method will fetch the table schema from the database which hold the field information and then fire the checking for table field. dbText hold the default data type.
Use the sub
Dim DB As Database
Dim RS As Recordset
Private Sub Command1_Click()
Call UpdateTableField(“Table1”, “RoomID2”)
End Sub
Private Sub Form_Initialize()
Set DB = OpenDatabase(“F:\Manoj\Code\DB-AUTO-FIELD-ACCES\db3.mdb”)
End Sub
A new field “RoomID2” will be created in the ‘Table1‘ Table.
Download the Source Code