Access Database Design and Programming, Second Edition

Creating an Index

Indexes are created using the CreateIndex method for a TableDef object. Here is the syntax:

Set IndexVar = TableDefVar.CreateIndex([IndexName])

Creating an index by itself does nothing. We must append one or more fields to the Fields collection of the index in order to actually index the table. Moreover, the order in which the fields are appended (when there is more than one field) has an effect on the index order. This is demonstrated in Example 15.3, in which a new index called PriceTitle is added to the BOOKS table.

Example 15-3. A CreateIndex Method Example

                  Sub exaCreateIndex()

Dim db As DATABASE
Dim tdf As TableDef
Dim idx As INDEX
Dim fld As Field

Set db = CurrentDb
Set tdf = db.TableDefs!BOOKS

' Create index by the name of PriceTitle
Set idx = tdf.CreateIndex("PriceTitle")

' Append the price and then the Title fields
' to the Fields collection of the index
Set fld = idx.CreateField("Price")
idx.Fields.Append fld
Set fld = idx.CreateField("Title")
idx.Fields.Append fld

' Append the index to the indexes collection 
' for BOOKS
tdf.Indexes.Append idx

End Sub

Figure 15.2 shows the result of running the program from Example 15.3. (To view this dialog box, open the BOOKS table in design view and select the Indexes option from the View menu.) The figure shows clearly why we first create two fields—Price and Title—and append them, in that order, to the Fields collection of the index.

Figure 15-2. Indexes view of BOOKS table from running exaCreateIndex

As we discussed ...