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