Option Compare Database

Option Explicit

 

Sub Create_tbl__ChangeTracker()

    Dim db As DAO.Database

    Dim fld As DAO.Field

    Dim idx As DAO.Index

    Dim tdf As DAO.TableDef

    '

    Set db = CurrentDb

    Set tdf = db.CreateTableDef("tbl__ChangeTracker")

    With tdf

        ' ID is AutoNumber and Primary Key

        Set fld = .CreateField("ID", dbLong)

        fld.Attributes = dbAutoIncrField

        .Fields.Append fld

        Set idx = .CreateIndex("ID")

        idx.Fields = "ID"

        idx.Primary = True

        .Indexes.Append idx

        '

        ' add remaining fields

        Set fld = .CreateField("FormName", dbText, 64)

        .Fields.Append fld

        Set fld = .CreateField("MyTable", dbText, 64)

        .Fields.Append fld

        Set fld = .CreateField("MyField", dbText, 64)

        .Fields.Append fld

        Set fld = .CreateField("MyKey", dbText, 64)

        .Fields.Append fld

        Set fld = .CreateField("ChangedOn", dbDate)

        .Fields.Append fld

        Set fld = .CreateField("FieldName", dbText, 64)

        .Fields.Append fld

        Set fld = .CreateField("Field_OldValue", dbText, 255)

        fld.AllowZeroLength = True

        .Fields.Append fld

        Set fld = .CreateField("Field_NewValue", dbText, 255)

        fld.AllowZeroLength = True

        .Fields.Append fld

        Set fld = .CreateField("UserChanged", dbText, 128)

        .Fields.Append fld

        Set fld = .CreateField("CompChanged", dbText, 128)

        .Fields.Append fld

    End With

    db.TableDefs.Append tdf

    Set idx = Nothing

    Set fld = Nothing

    Set tdf = Nothing

    Set db = Nothing

End Sub