Indexing Tables

[Mark]

Is there a way to add an index to an existing table using
VBA?

If so, what does the code look like?


Thank you, Mark

 

[Allen Browne]

This example shows how to create three indexes:
- a primary key index;
- a single field index;
- a multi-field index.

The fields must already exist in the table. The CreateField() refers to
creating the entry in the Fields collection of the Index, not creating
another fieldin the table.


Sub CreateIndexesDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("Table1")

'1. Primary key index.
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("ID")
.Unique = False
.Primary = True
End With
tdf.Indexes.Append ind

'2. Single-field index.
Set ind = tdf.CreateIndex("Inactive")
ind.Fields.Append ind.CreateField("Inactive")
tdf.Indexes.Append ind

'3. Multi-field index.
Set ind = tdf.CreateIndex("FullName")
With ind
.Fields.Append .CreateField("Surname")
.Fields.Append .CreateField("FirstName")
End With
tdf.Indexes.Append ind

'Refresh the display of this collection.
tdf.Indexes.Refresh

'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

 

[Mark]

Allen -

That looks great. Thank you.

One question:

In the primary key example (which is the one of most
interest to me), what does the "PrimaryKey" and "ID"
represent?

It looks like we are using the field "PrimaryKey" in
Table1, and renaming field as "ID". So Table1 before the
indexing example has a field called "PrimaryKey". But
after, Table1 has a field called "ID" which is essntially
the "PrimaryKey" but is the indexed field with attributes.

Am I close?

Thanks again Mark

 

[Allen Browne]

The field being indexed is named "ID" in the example.
Substitute the name of your field.

The index that's created is called "PrimaryKey". That's the name Access
generally uses, but you don't have to use that name: if the Primary property
is true, it is the primary key index.

 

[Marshall Barton]

Is there a way to add an index to an existing table using
VBA?

An alternative to using DAO is to Execute the SQL DDL
statement ALTER TABLE with an appropriate CONSTRAINT
clause. See Help for all the options and some examples.

If you're not particularly comfortable SQL, you will
probably want to just file this for future consideration and
stick with Allen's approach for now.

 

[Mark]

Thanks for the different "look".

It's a matter of time - would love to learn the SQL
approach, but there is a time element involved. I'll
look just in case it is easy.

Thanks again.

 

[Marshall Barton]

Thanks for the different "look".

It's a matter of time - would love to learn the SQL
approach, but there is a time element involved. I'll
look just in case it is easy.

Most things are easy - when you know how ;-)

Here's a couple of lines form a procedure I used to
reconfigure a delivered data db:

'Get rid of existing PK
DataDB.Execute "ALTER TABLE Quotes DROP CONSTRAINT PK"
' Add new ID field and copy the values from the old field
DataDB.Execute "ALTER TABLE Quotes ADD COLUMN ID LONG"
DataDB.Execute "UPDATE Quotes SET ID=CLng(OldID)"
' Make the new field the primry key
DataDB.Execute "ALTER TABLE Quotes ADD CONSTRAINT PK PRIMARY
KEY (ID)"
' Delete the old field
DataDB.Execute "ALTER TABLE Quotes DROP COLUMN OldID"