Unique Property Example

This example sets the Unique property of a new Index object to True, and appends the Index to the Indexes collection of the Employees table. It then enumerates the Indexes collection of the TableDef and the Properties collection of each Index. The new Index will only allow one record with a particular combination of Country, LastName, and FirstName in the TableDef.

Sub UniqueX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim idxNew As Index
    Dim idxLoop As Index
    Dim prpLoop As Property

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

    With tdfEmployees
        ' Create and append new Index object to the Indexes 
        ' collection of the Employees table.
        Set idxNew = .CreateIndex("NewIndex")

        With idxNew
            .Fields.Append .CreateField("Country")
            .Fields.Append .CreateField("LastName")
            .Fields.Append .CreateField("FirstName")
            .Unique = True
        End With

        .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

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

        Next idxLoop

        ' Delete new Index because this is a demonstration.
        .Indexes.Delete idxNew.Name
    End With

    dbsNorthwind.Close

End Sub