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 (Microsoft Jet workspaces only).

Settings and Return Values

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

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.

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-, snapshot-, or forward-only–type Recordset object by using an SQL statement with an ORDER BY clause.

Notes

You don't have to create indexes for tables. With 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 because 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 Recordset is opened.

See Also

Append method, Index object, Primary property, Seek method, Sort property, TableDef object.

Example

This example uses the Index property to set different record orders for a table-type Recordset.

Sub IndexPropertyX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim rstEmployees As Recordset
    Dim idxLoop As Index

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees")
    Set tdfEmployees = dbsNorthwind.TableDefs!Employees

    With rstEmployees

        ' Enumerate Indexes collection of Employees table.
        For Each idxLoop In tdfEmployees.Indexes
            .Index = idxLoop.Name
            Debug.Print "Index = " & .Index
            Debug.Print "    EmployeeID - PostalCode - Name"
            .MoveFirst

            ' Enumerate Recordset to show the order of records.
            Do While Not .EOF
                Debug.Print "        " & !EmployeeID & " - " & _
                    !PostalCode & " - " & !FirstName & " " & _
                    !LastName
                .MoveNext
            Loop

        Next idxLoop

        .Close
    End With

    dbsNorthwind.Close

End Sub

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 reference to current database.
    Set dbs = CurrentDb
    ' Create table-type Recordset object.
    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
    rst.Close
    Set dbs = Nothing
End Sub