CreateIndex Method Example

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.

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