Creating New Field Dynamically

[Jonesen]

Can anybody see what is wrong with my code? I'm simply trying to create a
new field (ID)in an existing table (tbl1) in the backend database. I've
tried some many variations my head is spinning. I'm sure I'm missing
something simple but I can't see it. Thanks

Dim wrkJet As Workspace
Dim Db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set Db = wrkJet.OpenDatabase("C:\Backend.MDB", True)
Set tdf = Db.TableDefs("tbl1")
Set idx = tdf.CreateIndex("IDType")
With idx
.Fields.Append .CreateField("IDType", dbText)
End With
tdf.Indexes.Append idx
Db.Close
Set wrkJet = Nothing
Set Db = Nothing

 

[Marshall Barton]

Can anybody see what is wrong with my code? I'm simply trying to create a
new field (ID)in an existing table (tbl1) in the backend database. I've
tried some many variations my head is spinning. I'm sure I'm missing
something simple but I can't see it. Thanks

Dim wrkJet As Workspace
Dim Db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set Db = wrkJet.OpenDatabase("C:\Backend.MDB", True)
Set tdf = Db.TableDefs("tbl1")
Set idx = tdf.CreateIndex("IDType")
With idx
.Fields.Append .CreateField("IDType", dbText)
End With
tdf.Indexes.Append idx
Db.Close
Set wrkJet = Nothing
Set Db = Nothing

Your code is trying to create an index on the field IDType.
not create the field in the table, which must be done first.

Set tdf = Db.TableDefs("tbl1")
Set fld = tdf.CreateField(""IDType". dbText, 255)
tdf.Fields.Append.fld

If you also want to create the index on that field:

Set idx = tdf.CreateIndex("IDType")
With idx
.Fields.Append .CreateField("IDType")
End With
tdf.Indexes.Append idx