IgnoreNulls, Unique Properties Example (MDB)

The following example adds a new index to a TableDef object and sets the IgnoreNulls and Unique properties to True (–1):

Sub NewIndex()
    Dim dbs As Database, tdf As TableDef
    Dim idx As Index, fldID As Field, fldName As Field

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Products table.
    Set tdf = dbs.TableDefs!Products
    ' Return Index object that points to new index.
    Set idx = tdf.CreateIndex("IDName")
    ' Create and append index fields.
    Set fldID = idx.CreateField("ProductID")
    Set fldName = idx.CreateField("ProductName")
    idx.Fields.Append fldID
    idx.Fields.Append fldName
    ' Set index properties.
    idx.IgnoreNulls = True
    idx.Unique = True
    ' Append new Index object to Indexes collection.
    tdf.Indexes.Append idx
    Set dbs = Nothing
End Sub