How to create index on existing field

[Guest]

How can I add an index on an existing field programmatically? The .CreateField method assumes that the field is being created as part of the Index collection. I can manually add/remove indexes on existing fields, but I need a way to do it programmatically for remote distribution of changes.

 

[Marshall Barton]

How can I add an index on an existing field programmatically? The .CreateField method assumes that the field is being created as part of the Index collection. I can manually add/remove indexes on existing fields, but I need a way to do it programmatically for remote distribution of changes.

You are misreading the creating a field Help topic. It's
not trying to get you to create a field in your table, it's
the table's Index collection that you have to create the
field object in. The index needs to know which field in the
table to use to do its job so you have to create the
field(s) there too.

Try rereading the Help and examples and see if you get a
clearer picture now.

Beside using CreateIndex and CreateField, you
can also use an SQL DDL statement,
CREATE INDEX
or
ALTER TABLE ADD CONSTRAINT

and if you use the ALTER TABLE ADD COLUMN statement to add a
new field to the table, you can use a CONSTRINT clause to
add the index at the same time.

 

[Guest]

Well, I am sure I don't understand the help very well, but I do understand that the field is created in the table's index collection if I use the .CreateField method. However, I also know that I can create a field in the table manually (in table design view), then later go back and add an index, also manually

Doesn't a field reside in the table's fields collection when it is created without an index? If so, is the field actually moved to the index collection when I manually add the index? I am just trying to find a way to add the index after the fact by issuing a "hotfix" without having to get access to table design view.

 

[Douglas J. Steele]

Even though the field may exist in the TableDef's Fields collection, you
still have to create a field to add to the Index's Fields collection.

Here's a slightly abridged version from the Help file:

Sub CreateIndexX()

Dim dbsNorthwind As Database
Dim tdfEmployees As TableDef
Dim idxCountry As Index

Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set tdfEmployees = dbsNorthwind!Employees

With tdfEmployees
' Create first Index object, create and append Field
' objects to the Index object, and then append the
' Index object to the Indexes collection of the
' TableDef.
Set idxCountry = .CreateIndex("CountryIndex")

With idxCountry
.Fields.Append .CreateField("Country")
.Fields.Append .CreateField("LastName")
.Fields.Append .CreateField("FirstName")
End With
.Indexes.Append idxCountry

End With

dbsNorthwind.Close

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well, I am sure I don't understand the help very well, but I do understand

that the field is created in the table's index collection if I use the
..CreateField method. However, I also know that I can create a field in the
table manually (in table design view), then later go back and add an index,
also manually

Doesn't a field reside in the table's fields collection when it is created

without an index? If so, is the field actually moved to the index collection
when I manually add the index? I am just trying to find a way to add the
index after the fact by issuing a "hotfix" without having to get access to
table design view.

 

[Dirk Goldgar]

Well, I am sure I don't understand the help very well, but I do
understand that the field is created in the table's index collection
if I use the .CreateField method. However, I also know that I can
create a field in the table manually (in table design view), then
later go back and add an index, also manually

Doesn't a field reside in the table's fields collection when it is
created without an index? If so, is the field actually moved to the
index collection when I manually add the index? I am just trying to
find a way to add the index after the fact by issuing a "hotfix"
without having to get access to table design view.

I think what you're not understanding is that the TableDef object -- the
definition of the table itself -- has a Fields collection that
represents all the fields in the table, and the Index object -- the
definition of an index on the table -- *also* has a Fields collection,
which represents the fields (selected from the fields in the table) that
are part of this index. The two Fields collections are different
things.

If you were creating a table and index in code, you would first create
the TableDef object and add fields to it. Then you would create the
index and add fields -- with names chosen from among the fields you
added to the TableDef -- to that. The Index.CreateField method doesn't
actually create a new field in the table, it just creates a field object
that can be added to the index.

 

[Guest]

Thank you. Your last statement clarified all. I was getting some sort of "name already exists" error when I was trying to create the field object in the index collection, so I assumed that it was because the field already existed in the table. Having copied in a fresh set of code that evidently resolved whatever syntax error was generating that message, everything works now.