Recordset.Index Property (DAO)

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.

Note

• 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.

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 

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

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: " & !ProductID & vbCr & _ 
            "Name: " & !ProductName & vbCr & vbCr & _ 
            "Enter a product ID between " & intFirst & _ 
            " and " & intLast & "." 
         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 

The following example shows how to use the Seek method to find a record in a linked table.

Sample code provided by: The Microsoft Access 2010 Programmer’s Reference | About the Contributors

Sub TestSeek()
    ' Get the path to the external database that contains
    ' the tblCustomers table we're going to search.
    Dim strMyExternalDatabase
    Dim dbs    As DAO.Database
    Dim dbsExt As DAO.Database
    Dim rst    As DAO.Recordset
    Dim tdf    As DAO.TableDef
    
    Set dbs = CurrentDb()
    Set tdf = dbs.TableDefs("tblCustomers")
    strMyExternalDatabase = Mid(tdf.Connect, 11)
    
    'Open the database that contains the table that is linked
    Set dbsExt = OpenDatabase(strMyExternalDatabase)
    
    'Open a table-type recordset against the external table
    Set rst = dbsExt.OpenRecordset("tblCustomers", dbOpenTable)
    
    'Specify which index to search on
    rst.Index = "PrimaryKey"
    
    'Specify the criteria
    rst.Seek "=", 123
    
    'Check the result
    If rst.NoMatch Then
        MsgBox "Record not found."
    Else
        MsgBox "Customer name: " & rst!CustName
    End If
    
    rst.Close
    dbs.Close
    dbsExt.Close
    Set rst = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    
    
End Sub

About the Contributors

Wrox Press is driven by the Programmer to Programmer philosophy. Wrox books are written by programmers for programmers, and the Wrox brand means authoritative solutions to real-world programming problems.