Primary Property

Applies To Index object.

Description

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

Settings and Return Values

The setting or return value is a Boolean that is True if the Index object represents a primary index.

The Primary property setting is read/write for a new Index object not yet appended to a collection and read-only for an existing Index object in an Indexes collection. If the Index object is appended to the TableDef object but the TableDef object isn't appended to the TableDefs collection, the Index property is read/write.

Remarks

A primary index consists of one or more fields that uniquely identify all records in a table in a predefined order. Because the index field must be unique, the Unique property of the Index object is set to True. If the primary 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. A primary index consists of a key for the table and usually contains the same fields as the primary key.

Note You don't have to create indexes for tables, but in large, unindexed tables, accessing a specific record can take a long time. 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. When you create a new table in your database, it's a good idea to create an index on one or more fields that uniquely identify each record, and then set the Primary property of the Index object to True.

When you set a primary key for a table, the primary key is automatically defined as the primary index for the table.

See Also Attributes property, Clustered property, Unique property.

Specifics (Microsoft Access)

In Microsoft Access, when you set a primary key for a table in table Design view, the primary key is automatically defined as the primary index.

Example

This example uses the Primary property to designate a new Index in a new TableDef as the primary Index for that table. Note that setting the Primary property to True automatically sets Unique and Required properties to True as well.

Sub PrimaryX()

    Dim dbsNorthwind As Database
    Dim tdfNew As TableDef
    Dim idxNew As Index
    Dim idxLoop As Index
    Dim fldLoop As Field
    Dim prpLoop As Property

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' Create and append a new TableDef object to the
    ' TableDefs collection of the Northwind database.
    Set tdfNew = dbsNorthwind.CreateTableDef("NewTable")
    tdfNew.Fields.Append tdfNew.CreateField("NumField", _
        dbLong, 20)
    tdfNew.Fields.Append tdfNew.CreateField("TextField", _
        dbText, 20)
    dbsNorthwind.TableDefs.Append tdfNew

    With tdfNew
        ' Create and append a new Index object to the
        ' Indexes collection of the new TableDef object.
        Set idxNew = .CreateIndex("NumIndex")
        idxNew.Fields.Append idxNew.CreateField("NumField")
        idxNew.Primary = True
        .Indexes.Append idxNew
        Set idxNew = .CreateIndex("TextIndex")
        idxNew.Fields.Append idxNew.CreateField("TextField")
        .Indexes.Append idxNew

        Debug.Print .Indexes.Count & " Indexes in " & _
            .Name & " TableDef"

        ' Enumerate Indexes collection.
        For Each idxLoop In .Indexes

            With idxLoop
                Debug.Print "    " & .Name

                ' Enumerate Fields collection of each Index
                ' object.
                Debug.Print "        Fields"
                For Each fldLoop In .Fields
                    Debug.Print "            " & fldLoop.Name
                Next fldLoop

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

        Next idxLoop

    End With

    dbsNorthwind.TableDefs.Delete tdfNew.Name
    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example checks the Primary property of each index on a Products table, and prints the fields making up the primary key:

Sub SetIndex()
    Dim dbs As Database, tdf As TableDef, fld As Field
    Dim idx As Index

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Products table.
    Set tdf = dbs.TableDefs!Products
    ' Enumerate through Indexes collection of TableDef object.
    For Each idx In tdf.Indexes
        ' Check Primary property of Recordset object.
        If idx.Primary Then
            For Each fld In idx.Fields
                Debug.Print fld.Name
            Next fld
        End If
    Next idx
    Set dbs = Nothing
End Sub