Index Properties

An Index object can have one or more combinations of several property settings that influence its behavior. For example, the Unique property can be set, which ensures that only one record with a particular value (or combination of values, if the index consists of multiple fields) can exist. You can set the Primary property to True to designate the index as the primary key of the table. Because a primary key must uniquely identify each record, setting an index’s Primary property to True automatically sets its Unique property to True.

Because a primary key is by definition a unique index, here’s how the Primary property and the Unique property differ.

The main differences are that the fields that make up the primary key of a table can’t contain Null values, and that only one primary key can be designated per table. These restrictions don’t apply to an index with the Unique property set to True. In addition to setting the Unique property of an index, you can set the IgnoreNulls property to True, which means Null values are not added to the index; consequently, using the Seek method to find a Null value always fails.

The following code adds an SSN# field and then creates a unique index for SSN#. The index forces all non-Null values to be unique, but allows Null values when the value of the SSN# field isn’t known. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database
Dim tdf As TableDef, idx As Index
Dim fld As Field

Set dbs = OpenDatabase(strDbPath)
Set tdf = dbs.TableDefs("Employees")
Set fld = tdf.CreateField("SSN#", dbText, 11)
tdf.Fields.Append fld

' Create index on SSN# field.
Set idx = tdf.CreateIndex("IX_SSN#")
Set fld = idx.CreateField("SSN#")
With idx
	.Fields.Append fld
	.Unique = True
	.IgnoreNulls = True
End With
tdf.Indexes.Append idx