Databases Reference
In-Depth Information
15.3.2 Changing the Properties of an Existing Table or Field
I have remarked that some properties that are read/write before the object is appended to
its collection become read-only after appending. One such example is the Type property
of a field. On the other hand, the Name property of a field can be changed. This is an
example of a change that can be made using DAO but not by using SQL.
15.4 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
