Creating New Field in Backend Table Problems.

[Gibson]

I am simply trying to add a field to an existing backend table using the
following code. When I get to the last line to Append idx I receive an error
message "Invalid field definition "Field1 in definition of Index or
relationship. I think I am making this more difficult than it should be.
Can anybody see what I am doing wrong? Thank you.

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set Db = wrkJet.OpenDatabase("C:\Backend.MDB", True)

Set tdf = Db.TableDefs("tbl1")
Set idx = tdf.CreateIndex("Field1")
With idx
.Fields.Append .CreateField("Field1", dbText, 20)
End With
tdf.Indexes.Append idx

 

[Marshall Barton]

I am simply trying to add a field to an existing backend table using the
following code. When I get to the last line to Append idx I receive an error
message "Invalid field definition "Field1 in definition of Index or
relationship. I think I am making this more difficult than it should be.
Can anybody see what I am doing wrong? Thank you.

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set Db = wrkJet.OpenDatabase("C:\Backend.MDB", True)

Set tdf = Db.TableDefs("tbl1")
Set idx = tdf.CreateIndex("Field1")
With idx
.Fields.Append .CreateField("Field1", dbText, 20)
End With
tdf.Indexes.Append idx

Assuming the table really has a field named field1, the only
thing obvious that strikes me is that the field type and
size arguments are not used when creating a field for an
index.

If that doesn't get past the problem, try creating the field
object as a separate step??

 

[George Nicholson]

Creating an index doesn't create the field. You need to use tdf.CreateField
and then append the field before you can create (or, more accurately,
Append) an index based on it. Otherwise you're trying to append an index on
a field that doesn't exist.

HTH,

 

[Gibson]

Thanks guys for the help. You were both right. I was trying to create the
field so it wasn't in the table. Once I change to .CreateField everything
worked fine. Thanks again for the help.