Creating New Field in Backend Table Problems.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gibson - 05 Dec 2005 18:45 GMT

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

Reply to this Message

Marshall Barton - 05 Dec 2005 19:57 GMT

> 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
[quoted text clipped - 11 lines]
> 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??

Signature

Marsh
MVP [MS Access]

Reply to this Message

George Nicholson - 05 Dec 2005 20:19 GMT

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,

Signature

George Nicholson

Remove 'Junk' from return address.

> 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
[quoted text clipped - 11 lines]
> End With
> tdf.Indexes.Append idx

Reply to this Message

Gibson - 05 Dec 2005 20:55 GMT

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.

> 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
[quoted text clipped - 17 lines]
>> End With
>> tdf.Indexes.Append idx

Reply to this Message