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