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 idxEnd 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 ...
Get Access Database Design and Programming, Second Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.