Index.Unique property (DAO)

Applies to: Access 2013, Office 2013

Sets or returns a value that indicates whether an Index object represents a unique (key) index for a table (Microsoft Access workspaces only).

Syntax

expression .Unique

expression A variable that represents an Index object.

Remarks

This property setting is read/write until the object is appended to a collection, after which it's read-only.

A unique index consists of one or more fields that logically arrange all records in a table in a unique, predefined order. If the index consists of one field, values in that field must be unique for the entire table. If the index consists of more than one field, each field can contain duplicate values, but each combination of values from all the indexed fields must be unique.

If both the Unique and Primary properties of an Index object are set to True, the index is unique and primary: It uniquely identifies all records in the table in a predefined, logical order. If the Primary property is set to False, the index is a secondary index. Secondary indexes (both key and nonkey) logically arrange records in a predefined order without serving as an identifier for records in the table.

Note

• You don't have to create indexes for tables, but in large, unindexed tables, accessing a specific record can take a long time.
• Records retrieved from tables without indexes are returned in no particular sequence.
• The Attributes property of each Field object in the Index object determines the order of records and consequently determines the access techniques to use for that Index object.
• A unique index helps optimize finding records.
• Indexes don't affect the physical order of a base table; indexes affect only how the records are accessed by the table-type Recordset object when a particular index is chosen or when the Microsoft Access database engine creates Recordset objects.

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