Access VBA Add a column if not there

Murray Brown
Murray Brown used Ask the Experts™

What Access VBA code would I use to check if a column called "CreatedAt" exists in a table called "GoodReceived" and  then create it as a Date/Time column if not. I want the new column to have a default value of Now()
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Private Sub createField_Click()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim blnFieldExists As Boolean
Set dbs = CurrentDb()
Set tdf = dbs.TableDefs("GoodReceived")
For Each fld In tdf.Fields
If fld.Name = "CreatedAt" Then
blnFieldExists = True
Exit For
End If
If Not blnFieldExists Then
    Set fld = tdf.CreateField("CreatedAt", dbDate)
    fld.DefaultValue = "=Now()"
    tdf.Fields.Append fld
End If
End Sub

Open in new window

Murray Developer


Thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial