Ex2: VBA - Modify existing tables using DAO

Add and delete fields to existing tables.

Option Compare Database
Option Explicit

'Constants for examining how a field is indexed.
Private Const intcIndexNone As Integer = 0
Private Const intcIndexGeneral As Integer = 1
Private Const intcIndexUnique As Integer = 3
Private Const intcIndexPrimary As Integer = 7

Function ModifyTableDAO()
    'Purpose:   How to add and delete fields to existing tables.
    'Note:      Requires the table created by CreateTableDAO() above.
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    'Initialize
    Set db = CurrentDb()

    Set tdf = db.TableDefs("tblDaoContractor")
    
    'Add a field to the table.
    tdf.Fields.Append tdf.CreateField("TestField", dbText, 80)
    Debug.Print "Field added."
    
    'Delete a field from the table.
    tdf.Fields.Delete "TestField"
    Debug.Print "Field deleted."
    
    'Clean up
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function