CreateIndex Method

Applies To TableDef object.

Description

Creates a new Index object (Microsoft Jet workspaces only).

Syntax

Set index = tabledef.CreateIndex(name)

The CreateIndex method syntax has these parts.

Part	Description

index	An object variable that represents the index you want to create.
tabledef	An object variable that represents the TableDef object you want to use to create the new Index object.
name	Optional. A Variant (String subtype) that uniquely names the new Index object. See the Name property for details on valid Index names.

Remarks You can use the CreateIndex method to create a new Index object for a TableDef object. If you omit the optional name part when you use CreateIndex, you can use an appropriate assignment statement to set or reset the Name property before you append the new object to a collection. After you append the object, you may or may not be able to set its Name property, depending on the type of object that contains the Indexes collection. See the Name property topic for more details.

If name refers to an object that is already a member of the collection, a run-time error occurs when you use the Append method.

To remove an Index object from a collection, use the Delete method on the collection.

See Also Append method, CREATE INDEX statement, Delete method, Index object, Name property.

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

Example (Microsoft Access)

The following example creates a new Index object on an Employees table. The new index consists of two fields, LastName and FirstName.

Sub NewIndex()
    Dim dbs As Database, tdf As TableDef
    Dim idx As Index
    Dim fldLastName As Field, fldFirstName As Field

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Employees table.
    Set tdf = dbs.TableDefs!Employees
    ' Return reference to new index.
    Set idx = tdf.CreateIndex("FullName")
    ' Create and append index fields.
    Set fldLastName = idx.CreateField("LastName", dbText)
    Set fldFirstName = idx.CreateField("FirstName", dbText)
    idx.Fields.Append fldLastName
    idx.Fields.Append fldFirstName
    ' Append Index object and refresh collection.
    tdf.Indexes.Append idx
    tdf.Indexes.Refresh
    Set dbs = Nothing
End Sub