Adding field to existing Table via code

[Gibson]

I am trying to create a new field in the backend .mdb via code. Being
unexperienced at this I have been using various portions of code I have
found in books and at web sites. The code below I understand creates a new
table and then puts the field NewField in that table. I tried OpenRecordset
with the name of the table I want to open instead of CreateTableDef thinking
that may open the table, no luck. How can I simply open an existing table
instead of creating a new one and how do I add properties like caption and
field size. Thanks so much for any suggestions.

Set dbsData = OpenDatabase("C:\Program Files\Directory\BackEnd.MDB")
Set tdfNew = dbsData.CreateTableDef("NewTable")
With tdfNew
.Fields.Append .CreateField("NewField", dbText)

dbsData.TableDefs.Append tdfNew
End With

 

[Brian]

'Declare variables
Dim FilePath as String
Dim tblName as String
Dim fldName as String
Dim fldType as String
Dim fldSize as String
Dim fldAttributes as String
Dim idx As DAO.Index
Dim idxName as String
Dim idxPrimary as Boolean
Dim idxUnique as Boolean
Dim tdf As DAO.TableDef

'set values
FilePath = "C:\Program Files\Directory\BackEnd.MDB"
tblName = "ExistingTable"
fldName = "NewField"
fldType = dbLong '(e.g. only: dbLong = Long Integer; dbText = text, etc.)
fldSize = 'Specify an integer or Null
fldAttributes = '(specify constant name/number or Null - NO QUOTES)
idxName = "IndexName" '(specify value or Null)
idxPrimary ='True or False (leave out this line if idxName is null)
idxUnique = 'True or False (leave out this line if idxName is null)

'append field to table
Set dbData = DBEngine.Workspaces(0).OpenDatabase(FilePath, True)
Set tdf = dbData.TableDefs(tblName)
Set fld = tdf.CreateField(fldName, fldType)
If Not IsNull(fldAttributes) Then fld.Attributes = fldAttributes
tdf.Fields.Append fld
Set idx = tdf.CreateIndex(idxName)
If idxPrimary Then
idx.Primary = True
Else
idx.Primary = False
If idxUnique Then
idx.Unique = True
Else
idx.Unique = False
End If
End If
Set fld = idx.CreateField(fldName, fldType)
idx.Fields.Append fld
tdf.Indexes.Append idx

You can look up the constants for the attributes & field size in VBA help.
The principal reference you need is probably this, considering the code you
have already developed:

Set tdf = dbData.TableDefs(tblName)
Set fld = tdf.CreateField(fldName, fldType)