How to Add Field with Index

  • Thread starter Thread starter Jonathan Wood
  • Start date Start date
J

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
 
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.)
 
Allen said:
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)
Click to expand...

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
Click to expand...

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.)
Click to expand...

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.)
Click to expand...

Right.

Jon
 
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.
 
Paul said:
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.
Click to expand...

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
 
Jonathan Wood said:
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?
Click to expand...


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
 
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
 
Jonathan Wood said:
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).
Click to expand...

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.
 
Back
Top