Index Object

Description

Index objects specify the order of records accessed from database tables and whether or not duplicate records are accepted. Index objects also provide efficient access to data. For external databases, Index objects describe the indexes established for external tables.

Remarks

The Microsoft Jet database engine maintains all base table indexes automatically. Indexes are updated whenever you add, change, or delete records from the base table. Once the database is created, use the CompactDatabase method periodically to bring index statistics up-to-date.

The Jet database engine uses indexes when it joins tables and creates Recordset objects. Indexes determine the order of records returned by table-type Recordset objects, but they don't determine the order in which records are stored in the base table or the order of records returned from any other type of recordset.

When accessing a table-type Recordset object, you specify the order of records using the object's Index property. Set this property to the Name property setting of an existing Index object in the Indexes collection. This collection is contained by the TableDef object underlying the Recordset object that you're populating. For example, to order your table-type Recordset object by the ZIP_INDEX index, you could use the following code.

Set dbsBiblio = Workspaces(0).OpenDatabase("Biblio.mdb")
Set rstPublishers = dbsBiblio.OpenRecordset("Publishers", dbOpenTable)
rstPublishers.Index = "ZIP_INDEX"

Tip

You don't have to create indexes for a table, but for large, unindexed tables, accessing a specific record or processing joins can take a long time. Conversely, having too many indexes can slow down updates to the database as each of the table indexes is amended.

The Attributes property of each Field object in the index determines the order of records returned and consequently determines which access techniques to use for that index.

Each Field object in the Fields collection of an Index object is a component of the index. You define a new Index object by setting its properties before you append it to a collection, which makes the Index object available for subsequent use.

Note

You can modify the Name property setting of an existing Index object only if the Updatable property setting of the containing TableDef object is True.

Using an Index object and its properties, you can:

Use the Required property to determine whether the Field objects in the index require values that are not Null, then use the IgnoreNulls property to determine whether the Null values have index entries.
Use the Primary and Unique properties to determine the ordering and uniqueness of the Index object.

When you set a primary key for a table, the Jet database engine automatically defines it as the primary index. A primary index consists of one or more fields that uniquely identify all records in a table in a predefined order. Because the primary index field must be unique, the Jet database engine automatically sets the Unique property of the primary Index object to True. If the primary index consists of more than one field, each field can contain duplicate values, but the combination of values from all the indexed fields must be unique. A primary index consists of a key for the table and is always made up of the same fields as the primary key.

Important

Make sure your data complies with the attributes of your new index. If your index requires unique values, make sure that there are no duplicates in existing data records. If duplicates exist, the Jet database engine can't create the index, resulting in a trappable error when you attempt to use the Append method on the new index.

When you create a relationship that enforces referential integrity, the Jet database engine automatically creates an index with the Foreign property, set as the foreign key in the referencing table. After you've established a table relationship, the Jet database engine prevents additions or changes to the database that violate that relationship. If you set the Attributes property of the Relation object to allow cascade update and cascade delete operations, the Jet database engine updates or deletes records in related tables automatically.

Note

The Clustered property is ignored for databases that use the Jet database engine, which doesn't support clustered indexes.

Properties

Clustered Property, DistinctCount Property, Foreign Property, IgnoreNulls Property, Name Property, Primary Property, Required Property, Unique Property.

Methods

CreateField Method, CreateProperty Method.

See Also

Attributes Property; CreateIndex Method; Index Property, OrdinalPosition Property; Appendix, "Data Access Object Hierarchy."

Example

This example creates a new TableDef object and two new Field objects, appends the Field objects to the Fields collection in the new TableDef, and appends the TableDef to the TableDefs collection in the database. Then it creates a new primary Index object, includes the two Field objects in it, and appends the Index to the Indexes collection of the TableDef. Finally, the example enumerates the Index objects in the current database.


	
	
	

Function EnumerateIndex () As Integer
Dim dbsDefault As Database, tdfTest As TableDef
Dim fldOne As Field, fldTwo As Field, idxPrimary As Index
Dim I As Integer
' Get workspace and database.
Set dbsDefault = _
DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
' Create table with two fields.
Set tdfTest = dbsDefault.CreateTableDef("MyTable")
Set fldOne = tdfTest.CreateField("Field1", dbLong)
fldOne.Required = True ' No Null values allowed.
tdfTest.Fields.Append fldOne
Set fldTwo = tdfTest.CreateField("Field2", dbLong)
fldTwo.Required = True ' No Null values allowed.
tdfTest.Fields.Append fldTwo
dbsDefault.TableDefs.Append tdfTest
' Create primary index for those two fields.
Set idxPrimary = tdfTest.CreateIndex("MyIndex")
idxPrimary.Primary = True
Set fldOne = tdfTest.CreateField("Field1")
idxPrimary.Fields.Append fldOne

    Set fldTwo = tdfTest.CreateField("Field2")
    idxPrimary.Fields.Append fldTwo
    tdfTest.Indexes.Append idxPrimary
    ' Enumerate index and its fields.
    Debug.Print "Index: "; idxPrimary.Name
    Debug.Print "  Required: "; idxPrimary.Required
    Debug.Print "  IgnoreNulls: "; idxPrimary.IgnoreNulls
    Debug.Print "  Primary: "; idxPrimary.Primary
    Debug.Print "  Clustered: "; idxPrimary.Clustered
    Debug.Print "  Unique: "; idxPrimary.Unique
    Debug. Print "  Foreign: "; idxPrimary.Foreign
    Debug.Print
    Debug.Print "Fields in Index: ";
    For I = 0 To idxPrimary.Fields.Count - 1
        Debug.Print " "; idxPrimary.Fields(I).Name;
    Next I
    Debug.Print
    EnumerateIndex = True
End Function

This example opens a table-type Recordset and selects an index for the Recordset. By setting an index, the Microsoft Jet database engine returns records in the order specified by the index. Without an index, table-type Recordset objects return records from the database table in no particular order.

Dim dbsDefault As Database
Dim rstTitles as Recordset
' Get workspace and database.
Set dbsDefault = _
     DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
Set rstTitles = dbsDefault.OpenRecordset("Titles")
rstTitles.Index = "MyIndex"
.
.
.

Example (Microsoft Access)

The following example creates a new index on an Employees table.

Sub NewIndex()
    Dim dbs As Database, tdf As TableDef, idx As Index
    Dim fld As Field

    ' Return Database object pointing to current database.
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs!Employees
    Set idx = tdf.CreateIndex("LastNameIndex")
    Set fld = idx.CreateField("LastName")
    idx.Fields.Append fld
    tdf.Indexes.Append idx
End Sub