Index

Index objects specify the order of records accessed from database tables and whether or not duplicate records are accepted, providing efficient access to data. For external databases, Index objects describe the indexes established for external tables (Microsoft Access workspaces only).

Remarks

The Microsoft Access database engine uses indexes when it joins tables and creates Recordset objects. Indexes determine the order in which table-type Recordset objects return records, but they don't determine the order in which the Microsoft Access database engine stores records in the base table or the order in which any other type of Recordset object returns records.

With an Index object, you can:

Use the Required property to determine whether the Field objects in the index require values that are not Null, and then use the IgnoreNulls property to determine whether the null values have index entries.
Use the Primary and Unique properties to determine the ordering and uniqueness of the Index object.

The Microsoft Access database engine maintains all base table indexes automatically. It updates indexes whenever you add, change, or delete records from the base table. Once you create the database, use the CompactDatabase method periodically to bring index statistics up-to-date.

When accessing a table-type Recordset object, you specify the order of records using the object's Index property. Set this property to the Name property setting of an existing Index object in the Indexes collection. This collection is contained by the TableDef object underlying the Recordset object that you're populating.

Note

You don't have to create indexes for a table, but for large, unindexed tables, accessing a specific record or processing joins can take a long time. Conversely, having too many indexes can slow down updates to the database as each of the table indexes is amended.

The Attributes property of each Field object in the index determines the order of records returned and consequently determines which access techniques to use for that index.

Each Field object in the Fields collection of an Index object is a component of the index. To define a new Index object, set its properties before you append it to a collection, making the Index object available for subsequent use.

Note

You can modify the Name property setting of an existing Index object only if the Updatable property setting of the containing TableDef object is True.

When you set a primary key for a table, the Microsoft Access database engine automatically defines it as the primary index. A primary index consists of one or more fields that uniquely identify all records in a table in a predefined order. Because the primary index field must be unique, the Microsoft Access database engine automatically sets the Unique property of the primary Index object to True. If the primary index consists of more than one field, each field can contain duplicate values, but the combination of values from all the indexed fields must be unique. A primary index consists of a key for the table and is always made up of the same fields as the primary key.

Important

Make sure your data complies with the attributes of your new index. If your index requires unique values, make sure that there are no duplicates in existing data records. If duplicates exist, the Microsoft Access database engine can't create the index; a trappable error results when you attempt to use the Append method on the new index.

When you create a relationship that enforces referential integrity, the Microsoft Access database engine automatically creates an index with the Foreign property, set as the foreign key in the referencing table. After you've established a table relationship, the Microsoft Access database engine prevents additions or changes to the database that violate that relationship. If you set the Attributes property of the Relation object to allow cascading updates and cascading deletes, the Microsoft Access database engine updates or deletes records in related tables automatically.

Use the CreateIndex method on a TableDef object.
Use the CreateField method on the Index object to create a Field object for each field (column) to be included in the Index object.
Set Index properties as needed.
Append the Field object to the Fields collection.
Append the Index object to the Indexes collection.

Note

The Clustered property is ignored for databases that use the Microsoft Access database engine, which doesn't support clustered indexes.

Methods

Name	Description
CreateField	Creates a new Field object (Microsoft Access workspaces only).
CreateProperty	Creates a new user-defined Property object (Microsoft Access workspaces only).

Properties

Name	Description
Clustered	Sets or returns a value that indicates whether an Index object represents a clustered index for a table (Microsoft Access workspaces only). Read/write Boolean.
DistinctCount	Returns a value that indicates the number of unique values for the Index object that are included in the associated table (Microsoft Access workspaces only).
Fields	Returns a Fields collection that represents all stored Field objects for the specified object. Read/write.
Foreign	Returns a value that indicates whether an Index object represents a foreign key in a table (Microsoft Access workspaces only). .
IgnoreNulls	Sets or returns a value that indicates whether records that have Null values in their index fields have index entries (Microsoft Access workspaces only).
Name	Returns or sets the name of the specified object. Read/write String if the object has not been appended to a collection. Read-only String if the object has been appended to a collection.
Primary	Sets or returns a value that indicates whether an Index object represents a primary key index for a table (Microsoft Access workspaces only).
Properties	Returns the Properties collection of the specified object. Read-only.
Required	Sets or returns a value that indicates whether a Field object requires a non-Null value.
Unique	Sets or returns a value that indicates whether an Index object represents a unique (key) index for a table (Microsoft Access workspaces only).

Example

This example creates a new Index object, appends it to the Indexes collection of the Employees TableDef, and then enumerates the Indexes collection of the TableDef. Finally, it enumerates a Recordset, first using the primary Index, and then using the new Index. The IndexOutput procedure is required for this procedure to run.

Sub IndexObjectX()

Dim dbsNorthwind As Database

Dim tdfEmployees As TableDef

Dim idxNew As Index

Dim idxLoop As Index

Dim rstEmployees As Recordset

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

Set tdfEmployees = dbsNorthwind!Employees

With tdfEmployees

' Create new index, create and append Field

' objects to its Fields collection.

Set idxNew = .CreateIndex("NewIndex")

With idxNew

.Fields.Append .CreateField("Country")

.Fields.Append .CreateField("LastName")

.Fields.Append .CreateField("FirstName")

End With

' Add new Index object to the Indexes collection

' of the Employees table collection.

.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

Next idxLoop

Set rstEmployees = _

dbsNorthwind.OpenRecordset("Employees")

' Print report using old and new indexes.

IndexOutput rstEmployees, "PrimaryKey"

IndexOutput rstEmployees, idxNew.Name

rstEmployees.Close

' Delete new Index because this is a

' demonstration.

.Indexes.Delete idxNew.Name

End With

dbsNorthwind.Close

End Sub

Sub IndexOutput(rstTemp As Recordset, _

strIndex As String)

' Report function for FieldX.

With rstTemp

' Set the index.

.Index = strIndex

.MoveFirst

Debug.Print "Recordset = " & .Name & _

", Index = " & .Index

Debug.Print " EmployeeID - Country - Name"

' Enumerate the recordset using the specified

' index.

Do While Not .EOF

Debug.Print " " & !EmployeeID & " - " & _

!Country & " - " & !LastName & ", " & !FirstName

.MoveNext

Loop

End With

End Sub

This example uses the CreateIndex method to create two new Index objects and then appends them to the Indexes collection of the Employees TableDef object. It then enumerates the Indexes collection of the TableDef object, the Fields collection of the new Index objects, and the Properties collection of the new Index objects. The CreateIndexOutput function is required for this procedure to run.

Sub CreateIndexX()

Dim dbsNorthwind As Database

Dim tdfEmployees As TableDef

Dim idxCountry As Index

Dim idxFirstName As Index

Dim idxLoop As Index

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

Set tdfEmployees = dbsNorthwind!Employees

With tdfEmployees

' Create first Index object, create and append Field

' objects to the Index object, and then append the

' Index object to the Indexes collection of the

' TableDef.

Set idxCountry = .CreateIndex("CountryIndex")

With idxCountry

.Fields.Append .CreateField("Country")

.Fields.Append .CreateField("LastName")

.Fields.Append .CreateField("FirstName")

End With

.Indexes.Append idxCountry

' Create second Index object, create and append Field

' objects to the Index object, and then append the

' Index object to the Indexes collection of the

' TableDef.

Set idxFirstName = .CreateIndex

With idxFirstName

.Name = "FirstNameIndex"

.Fields.Append .CreateField("FirstName")

.Fields.Append .CreateField("LastName")

End With

.Indexes.Append idxFirstName

' Refresh collection so that you can access new Index

' objects.

.Indexes.Refresh

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

.Name & " TableDef"

' Enumerate Indexes collection.

For Each idxLoop In .Indexes

Debug.Print " " & idxLoop.Name

Next idxLoop

' Print report.

CreateIndexOutput idxCountry

CreateIndexOutput idxFirstName

' Delete new Index objects because this is a

' demonstration.

.Indexes.Delete idxCountry.Name

.Indexes.Delete idxFirstName.Name

End With

dbsNorthwind.Close

End Sub

Function CreateIndexOutput(idxTemp As Index)

Dim fldLoop As Field

Dim prpLoop As Property

With idxTemp

' Enumerate Fields collection of Index object.

Debug.Print "Fields in " & .Name

For Each fldLoop In .Fields

Debug.Print " " & fldLoop.Name

Next fldLoop

' Enumerate Properties collection of Index object.

Debug.Print "Properties of " & .Name

For Each prpLoop In .Properties

Debug.Print " " & prpLoop.Name & " - " & _

IIf(prpLoop = "", "[empty]", prpLoop)

Next prpLoop

End With

End Function