Share via

Recordset.Index Property

  • Article
Access Developer Reference

Sets or returns a value that indicates the name of the current Index object in a table-type Recordset object (Microsoft Access workspaces only).

Syntax

expression.Index

expression   A variable that represents a Recordset 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.

<<<<<<< HEAD ======= >>>>>>> 7f3a9c6c13e671f21bf63e8e33d124610e43b846
Bb208479.vs_note(en-us,office.12).gif  NotesBb208479.vs_note(en-us,office.12).gif  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 tableindexes affect only how the records are accessed by the table-type Recordset object when a particular index is chosen or when Recordset is opened.

Example

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

Visual Basic for Applications 
  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
     .<strong class="bterm">Index</strong> = idxLoop.Name
     Debug.Print "Index = " &amp; .Index
     Debug.Print "  EmployeeID - PostalCode - Name"
     .MoveFirst

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

  Next idxLoop

  .Close



End With


dbsNorthwind.Close


End Sub

This example demonstrates the Seek method by allowing the user to search for a product based on an ID number.

Visual Basic for Applications 
  Sub SeekX()

Dim dbsNorthwind As Database
Dim rstProducts As Recordset
Dim intFirst As Integer
Dim intLast As Integer
Dim strMessage As String
Dim strSeek As String
Dim varBookmark As Variant


Set dbsNorthwind = OpenDatabase("Northwind.mdb")
' You must open a table-type Recordset to use an index,
' and hence the Seek method.
Set rstProducts = _
dbsNorthwind.OpenRecordset("Products", dbOpenTable)


With rstProducts
' Set the index.
.Index = "PrimaryKey"


  ' Get the lowest and highest product IDs.
  .MoveLast
  intLast = !ProductID
  .MoveFirst
  intFirst = !ProductID

  Do While True
     ' Display current record information and ask user 
     ' for ID number.
     strMessage = "Product ID: " &amp; !ProductID &amp; vbCr &amp; _
        "Name: " &amp; !ProductName &amp; vbCr &amp; vbCr &amp; _
        "Enter a product ID between " &amp; intFirst &amp; _
        " and " &amp; intLast &amp; "."
     strSeek = InputBox(strMessage)

     If strSeek = "" Then Exit Do

     ' Store current bookmark in case the Seek fails.
     varBookmark = .Bookmark

     .Seek "=", Val(strSeek)

     ' Return to the current record if the Seek fails.
     If .NoMatch Then
        MsgBox "ID not found!"
        .Bookmark = varBookmark
     End If
  Loop

  .Close



End With


dbsNorthwind.Close


End Sub

See Also