Index Property

Applies To

Recordset Object, Table-Type Recordset Object.

Description

Sets or returns a value that indicates the name of the current Index object in a table-type Recordset object.

Settings and Return Values

The setting or return value is a string expression that evaluates to the name of an Index object in the Indexes collection of the Table or table-type Recordset object's TableDef object. The data type is String.

Remarks

Records in base tables aren't stored in any particular order. Setting the Index property changes the order of records returned from the database; it doesn't affect the order in which the records are stored.

For example, to set the Index property prior to using the Seek method on the Titles table:

Dim rstTitles as Recordset, dbsBiblio as Database
Set dbsBiblio = Workspaces(0).OpenDatabase("Biblio.mdb")
Set rstTitles = dbsBiblio.OpenRecordset("Titles",dbOpenTable)
rstTitles.Index = "PubID"
rstTitles.Seek "=", 3

The specified Index object must already be defined. If you set the Index property to an Index object that doesn't exist or if the Index property isn't set when you use the Seek method, a trappable error occurs.

Examine the Indexes collection of a TableDef object to determine what Index objects are available to table-type Recordset objects created from that TableDef object.

You can create a new index for the table by creating a new Index object, setting its properties, appending it to the Indexes collection of the underlying TableDef object, and then reopening the Recordset object.

Records returned from a table-type Recordset object can be ordered only by the indexes defined for the underlying TableDef object. To sort records in some other order, you can open a dynaset- or Snapshot-Type Recordset object using an SQL statement with an ORDER BY clause.

Tips

You don't have to create indexes for tables, but in large, unindexed tables, accessing a specific record or creating a Recordset object can take a long time. On the other hand, creating too many indexes slows down update, append, and delete operations as all indexes are automatically updated.
Records read from tables without indexes are returned in no particular sequence.
The Attributes property of each Field object in the Index object determines the order of records and consequently determines the access techniques to use for that index.
A unique index helps optimize finding records.
Indexes don't affect the physical order of a base table — indexes affect only how the records are accessed by the table-type Recordset object when a particular index is chosen or when the Microsoft Jet database engine creates Recordset objects.

See Also

Append Method, Index Object, Primary Property, Seek Method, Sort Property, TableDef Object.

Example

This example adds a new index to a TableDef object in the Biblio.mdb database, makes that index the current index, and invokes the Seek method based on that index. The names of the Field objects created and appended to the Fields collection of the Index object correspond to the names of current fields in the table.

Dim rstPublishers As Recordset, dbsBiblio As Database
Dim idxPubIDName As Index, fldPubID As Field, fldName As Field
Set dbsBiblio =  DBEngine.Workspaces(0).OpenDatabase("Biblio.mdb")
Set idxPubIDName = dbsBiblio.TableDefs("Publishers").CreateIndex _ 
    ("PubID Name")
Set fldPubID = idxPubIDName.CreateField("PubID")
idxPubIDName.Fields.Append fldPubID
Set fldName = idxPubIDName.CreateField("Name")
idxPubIDName.Fields.Append fldName
idxPubIDName.Unique = True
Debug.Print idxPubIDName.Fields    ' Show Fields property.
' Append the new Index to the Indexes collection.
dbsBiblio.TableDefs("Publishers").Indexes.Append idxPubIDName
'Open the table.
Set rstPublishers = dbsBiblio.OpenRecordset("Publishers",dbOpenTable)  
rstPublishers.Index = "PubID Name"    ' Set table's current index.
rstPublishers.Seek "=", "27", "Yourdon Press"  ' Look for Publisher.
If rstPublishers.NoMatch Then
    rstPublishers.MoveFirst
    Debug.Print "Not found"
Else
    Debug.Print rstPublishers!Name
End If

rstPublishers.Close

This example sets the index for rstOrders to the primary key. (PrimaryKey is the default name of an Index object if the primary key is set in the table's Design view.) Next, it locates the record with a matching key field value of 10,050. Notice that the current index must be set before certain operations, such as the Seek method, can be performed.

Dim dbsNorthwind As Database, rstOrders As Recordset
Set dbsNorthwind =  DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
Set rstOrders = dbsNorthwind.OpenRecordset("Orders", dbOpenTable)
rstOrders.Index = "PrimaryKey"    ' Set current index.
rstOrders.Seek "=", 10050    ' Locate record.
...
rstOrders.Close    ' Close recordset.
dbsNorthwind.Close    ' Close database.

Example (Microsoft Access)

The following example sets the index for a table-type Recordset object to the primary key. "PrimaryKey" is the default name of an Index object if the index corresponds to the primary key set in table Design view. Next, the procedure asks the user for a value to search on and locates the record with a matching key field value. Note that the current index must be set before certain operations, such as the Seek method, can be performed on a table-type Recordset object.

Sub UsePrimaryKey()
    Dim dbs As Database, rst As Recordset
    Dim fld as Field, strInput as String

    ' Return Database variable that points to current database.
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Orders", dbOpenTable)
    ' Set current index.
    rst.Index = "PrimaryKey"
    strInput = InputBox("Enter the OrderID on which to search.")
    ' Locate record.
    rst.Seek "=", strInput
    If Not rst.NoMatch Then
        For Each fld in rst.Fields
            Debug.Print fld.Name; "   "; fld.Value
        Next fld
    End If
    ' Close Recordset object.
    rst.Close
End Sub