Index Object, Indexes Collection Example

This example creates a new Index object, appends it to the Indexes collection of the Employees TableDef, and then enumerates the Indexes collection of the TableDef. Finally, it enumerates a Recordset, first using the primary Index, and then using the new Index. The IndexOutput procedure is required for this procedure to run.

Sub IndexObjectX()

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

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set tdfEmployees = dbsNorthwind!Employees

   With tdfEmployees
      ' Create new index, create and append Field 
      ' objects to its Fields collection.
      Set idxNew = .CreateIndex("NewIndex")

      With idxNew
         .Fields.Append .CreateField("Country")
         .Fields.Append .CreateField("LastName")
         .Fields.Append .CreateField("FirstName")
      End With

      ' Add new Index object to the Indexes collection 
      ' of the Employees table collection.
      .Indexes.Append idxNew
      .Indexes.Refresh

      Debug.Print .Indexes.Count & " Indexes in " & _
         .Name & " TableDef"

      ' Enumerate Indexes collection of Employees 
      ' table.
      For Each idxLoop In .Indexes
         Debug.Print "  " & idxLoop.Name
      Next idxLoop

      Set rstEmployees = _
         dbsNorthwind.OpenRecordset("Employees")

      ' Print report using old and new indexes.
      IndexOutput rstEmployees, "PrimaryKey"
      IndexOutput rstEmployees, idxNew.Name
      rstEmployees.Close

      ' Delete new Index because this is a 
      ' demonstration.
      .Indexes.Delete idxNew.Name
   End With

   dbsNorthwind.Close

End Sub

Sub IndexOutput(rstTemp As Recordset, _
   strIndex As String)
   ' Report function for FieldX.

   With rstTemp
      ' Set the index.
      .Index = strIndex
      .MoveFirst
      Debug.Print "Recordset = " & .Name & _
         ", Index = " & .Index
      Debug.Print "  EmployeeID - Country - Name"

      ' Enumerate the recordset using the specified 
      ' index.
      Do While Not .EOF
         Debug.Print "  " & !EmployeeID & " - " & _
            !Country & " - " & !LastName & ", " & !FirstName
         .MoveNext
      Loop

   End With

End Sub