Indexes Collection

  • Article
Access Developer Reference

An Indexes collection contains all the stored Index objects of a TableDef object (Microsoft Access workspaces only).

Remarks

When you access a table-type Recordset object, use the object's Index property to specify the order of records. Set this property to the Name property setting of an existing Index object in the Indexes collection of the the TableDef object underlying the Recordset object.

Bb177466.vs_note(en-us,office.12).gif  Note
You can use the Append or Delete method on an Indexes collection only if the Updatable property setting of the containing TableDef object is True.

After you create a new Index object, you should use the Append method to add it to the TableDef object's Indexes collection.

** Important **  Make sure your data complies with the attributes of your new index. If your index requires unique values, make sure that there are no duplicates in existing data records. If duplicates exist, the Microsoft Access database engine can't create the index; a trappable error results when you attempt to use the Append method on the new index.

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.

Visual Basic for Applications 
  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

This example uses the CreateIndex method to create two new Index objects and then appends them to the Indexes collection of the Employees TableDef object. It then enumerates the Indexes collection of the TableDef object, the Fields collection of the new Index objects, and the Properties collection of the new Index objects. The CreateIndexOutput function is required for this procedure to run.

Visual Basic for Applications 
  Sub CreateIndexX()

Dim dbsNorthwind As Database
Dim tdfEmployees As TableDef
Dim idxCountry As Index
Dim idxFirstName As Index
Dim idxLoop As Index


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


With tdfEmployees
' Create first Index object, create and append Field
' objects to the Index object, and then append the
' Index object to the Indexes collection of the
' TableDef.
Set idxCountry = .CreateIndex("CountryIndex")
With idxCountry
.Fields.Append .CreateField("Country")
.Fields.Append .CreateField("LastName")
.Fields.Append .CreateField("FirstName")
End With
.Indexes.Append idxCountry


  ' Create second Index object, create and append Field
  ' objects to the Index object, and then append the
  ' Index object to the Indexes collection of the
  ' TableDef.
  Set idxFirstName = .CreateIndex
  With idxFirstName
     .Name = "FirstNameIndex"
     .Fields.Append .CreateField("FirstName")
     .Fields.Append .CreateField("LastName")
  End With
  .Indexes.Append idxFirstName

  ' Refresh collection so that you can access new Index 
  ' objects.
  .Indexes.Refresh

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

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

  ' Print report.
  CreateIndexOutput idxCountry
  CreateIndexOutput idxFirstName

  ' Delete new Index objects because this is a
  ' demonstration.
  .Indexes.Delete idxCountry.Name
  .Indexes.Delete idxFirstName.Name



End With


dbsNorthwind.Close


End Sub


Function CreateIndexOutput(idxTemp As Index)


Dim fldLoop As Field
Dim prpLoop As Property


With idxTemp
' Enumerate Fields collection of Index object.
Debug.Print "Fields in " & .Name
For Each fldLoop In .Fields
Debug.Print "  " & fldLoop.Name
Next fldLoop


  ' Enumerate Properties collection of Index object.
  Debug.Print "Properties of " & .Name
  For Each prpLoop In .Properties
     Debug.Print "  " & prpLoop.Name & " - " & _
        IIf(prpLoop = "", "[empty]", prpLoop)
  Next prpLoop



End With


End Function