How to Add Field with Index

[Jonathan Wood]

Greetings,

I'm trying to add an indexed field to a table using VBA.

After considerable searching, I came up with the following code.
Unfortunately, I get an error when I attempt to add the index to the Indexes
collection: Run-time error '3409': Invalid field definition 'ShipCustomerID'
in definition of index or relationship.

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb

Set td = db.TableDefs("TempTable")
Set idx = td.CreateIndex("ShipCustomerID_Index")
Set fld = idx.CreateField("ShipCustomerID", dbText, 10)
fld.Required = False
idx.Fields.Append fld
idx.Primary = False
idx.Unique = False
idx.Required = False

td.Indexes.Append idx <== Error happens here

I don't see what is wrong with the field definition. Any help appreciated.

Also, if anyone know, I want this to be a foreign key. In Access, I guess
that means I need to create a relationship. I found various code for this
but no examples that just do this alone. A small code snippet would be
fantastic if anyone was so inclined.

Thanks!

Jonathan

 

[Allen Browne]

Presumably TempTable already exists, and has the ShipCustomerID field in it.
Therefore you don't need the line:
Set fld = idx.CreateField("ShipCustomerID", dbText, 10)

For examples of how to create a primary key index, a single-field index, and
a multi-field index, see the CreateIndexesDAO() function here:
http://allenbrowne.com/func-DAO.html#CreateIndexesDAO

The next example on that page -- CreateRelationDAO() -- shows how to create
a relation (primary key to foreign key.)

(Hopefully you do know how to use the Relationships window to do this, so
that this is something you can't just do at design time.)

 

[Jonathan Wood]

Presumably TempTable already exists, and has the ShipCustomerID field in
it. Therefore you don't need the line:
Set fld = idx.CreateField("ShipCustomerID", dbText, 10)

No. As stated in my subject line, I'm trying to add a field with an index.
The table exists but not the field.

For examples of how to create a primary key index, a single-field index,
and a multi-field index, see the CreateIndexesDAO() function here:
http://allenbrowne.com/func-DAO.html#CreateIndexesDAO

Thanks but, unfortunately, that's similar to the type of stuff I've been
looking at. It doesn't do exactly what I'm doing and therefore doesn't help
me understand the error I'm getting.

The next example on that page -- CreateRelationDAO() -- shows how to
create a relation (primary key to foreign key.)

That looks like it may be helpful. Thanks!

(Hopefully you do know how to use the Relationships window to do this, so
that this is something you can't just do at design time.)

Right.

Jon

 

[Paul Shapiro]

You have to add the field to the table before you can use the field in an
index. It's similar to the code you showed, but you create and append the
field to the tabledef, not the index.

 

[Jonathan Wood]

You have to add the field to the table before you can use the field in an
index. It's similar to the code you showed, but you create and append the
field to the tabledef, not the index.

This is driving me nuts. I'm an advanced developer who hasn't spent a lot of
time with Access and VBA. But I don't seem to be able to get this to work
with the piecemeal information I'm able to gather.

So I modified my code as shown below. But then I get the error: Run-time
error '3367': Cannot append. An object with that name already exists in the
collection.

Set td = db.TableDefs("TempTable")
Set idx = td.CreateIndex("ShipCustomerID_Index")
Set fld = td.CreateField("ShipCustomerID", dbText, 10)
fld.Required = False
td.Fields.Append Fld

idx.Fields.Append fld <== Error happens here

idx.Primary = False
idx.Unique = False
idx.Required = False
td.Indexes.Append idx

Since I just created the index, how can it already contain the object I'm
adding?

Thanks.

Jon

 

[Dirk Goldgar]

This is driving me nuts. I'm an advanced developer who hasn't spent a lot
of time with Access and VBA. But I don't seem to be able to get this to
work with the piecemeal information I'm able to gather.

So I modified my code as shown below. But then I get the error: Run-time
error '3367': Cannot append. An object with that name already exists in
the collection.

Set td = db.TableDefs("TempTable")
Set idx = td.CreateIndex("ShipCustomerID_Index")
Set fld = td.CreateField("ShipCustomerID", dbText, 10)
fld.Required = False
td.Fields.Append Fld

idx.Fields.Append fld <== Error happens here

idx.Primary = False
idx.Unique = False
idx.Required = False
td.Indexes.Append idx

Since I just created the index, how can it already contain the object I'm
adding?

The error message is misleading, but your problem is that you need to create
the field for the index from the index itself, like this:

'------ start of code snippet ------
Set fld = td.CreateField("ShipCustomerID", dbText, 10)
fld.Required = False
td.Fields.Append fld

Set idx = td.CreateIndex("ShipCustomerID_Index")
Set fld = idx.CreateField("ShipCustomerID")
idx.Fields.Append fld
' ....
td.Indexes.Append idx
'------ end of code snippet ------

Or, more concisely:

Set idx = td.CreateIndex("ShipCustomerID_Index")
idx.Fields.Append idx.CreateField("ShipCustomerID")

' ....
td.Indexes.Append idx

 

[Jonathan Wood]

Thanks Dirk for filling in the gaps for me.

The code looks very odd to me because it looks like I am creating an index
and two fields (since the field is created in both the tabledef and the
index).

But it appears that was what I was missing because it appears to work fine.

Thanks again.

Jon

 

[Dirk Goldgar]

Thanks Dirk for filling in the gaps for me.

The code looks very odd to me because it looks like I am creating an index
and two fields (since the field is created in both the tabledef and the
index).

I know what you mean, but in reality they are two different sorts of fields.
The field in the TableDef will hold data; the field in the Index object is
essentially just a statement that a particular field in the table
participates in the index.