Index Property

See Also   Example   Applies To   Specifics

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.