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. 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.

Settings and Return Values

The setting or return value is a Boolean expression that specifies a primary index. The data type is Boolean. True (-1) indicates that the Index object represents a primary index.

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.

Tip

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

In the Biblio.mdb database, this example creates a new Table and primary key Index object and then adds the Table to the TableDefs collection of a Database.

Dim dbsBiblio As Database , tdfBookTypes As TableDef
Dim idxPrimaryKey As Index, fldTypeNum As Field, fldType As Field
    ' Open the Database.
Set dbsBiblio =  DBEngine.Workspaces(0).OpenDatabase("Biblio.mdb")
Set tdfBookTypes = dbsBiblio.CreateTableDef("Book Types")
    ' Create and Set fldTypeNum field Set fldTypeNum =
     ' tdfBookTypes.CreateField("TypeNum",dbLong)

    ' Add fldTypeNum field to TableDef.
    tdfBookTypes.Fields.Append fldTypeNum
    ' Create and Set fldType field properties.
Set fldType = tdfBookTypes.CreateField("Type",dbText)
fldType.Size = 15    ' Set Size property of text field.
    ' Add fldType field to TableDef.
tdfBookTypes.Fields.Append fldType
Set idxPrimaryKey = tdfBookTypes.CreateIndex("PrimaryKey")
With idxPrimaryKey        ' Set properties in new Index.
   .Name = "PrimaryKey"
   .Unique = True
   .Primary = True
   .Fields = "TypeNum"
End With
    ' Add Index to TableDefs collection.
idxPrimaryKey.fields.Append idxPrimaryKey.CreateField("TypeNum")
tdfBookTypes.Indexes.Append idxPrimaryKey
' Append TableDef to database.
dbsBiblio.TableDefs.Append tdfBookTypes
dbsBiblio.Close

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 Database variable that points to current database.
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs!Products
    ' Enumerate through Indexes collection of TableDef.
    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
            Exit Sub
        End If
    Next idx
    MsgBox "No primary key defined."
End Sub