Creating and Deleting Indexes by Using DAO

As discussed in Chapter 2, “Introducing Data Access Objects,” a TableDef object can have one or more Index objects in its Indexes collection. Each Index object is in turn made up of one or more Field objects in its Fields collection.

Make sure you don’t confuse the Fields collection of the TableDef object itself with the Fields collection of any given Index object. All the Field objects in an Index object’s Fields collection must also be Field objects in the Fields collection of the TableDef object, but each Index object has its own Fields collection. These collections exist as a hierarchy of objects and collections.

Unlike some database systems, such as FoxPro, with Microsoft Jet you can’t create an index on an expression, such as a subset of a field or a calculated field.

It’s common to designate one field, or a combination of fields, as the primary key of a table at the time it’s created. The following function creates a primary key based on one or more fields in a table. The third argument to the function is a parameter array in which you can specify one or more field names to be included in the primary key:

Function AddPrimaryKeyToTable(tdf As TableDef, strPKName As String, _
	ParamArray varFieldName() As Variant) As Boolean
	' This procedure creates a table with a primary key.

	Dim fld As Field
	Dim idxPK As Index
	Dim intC As Integer
	Const conTableExists As Integer = 3010

	On Error GoTo Err_AddPrimaryKeyToTable

	' Create index.
	Set idxPK = tdf.CreateIndex(strPKName)

	For intC = 0 To UBound(varFieldName())
		' Create field in index.
		Set fld = idxPK.CreateField(varFieldName(intC))
		idxPK.Fields.Append fld
	Next

	' Set Primary property of index.
	idxPK.Primary = True

	' Append index.
	tdf.Indexes.Append idxPK
	AddPrimaryKeyToTable = True

	Exit_AddPrimaryKeyToTable:
		Exit Function

	Err_AddPrimaryKeyToTable:
		MsgBox "Error: " & Err & vbCrLf & Err.Description
		AddPrimaryKeyToTable = False
		Resume Exit_AddPrimaryKeyToTable
End Function

Note that you can use any valid Microsoft Jet name for the primary key. When you add a primary key to a table in the Microsoft Access user interface, the index is automatically named PrimaryKey, but you can change the name.

See Also For information about what constitutes a valid Microsoft Jet name, see “Guidelines for Naming DAO Objects” in Chapter 2, “Introducing Data Access Objects.”

In most cases, the name you give an index is not significant. However, if you are opening table-type Recordset objects in code and want to specify the Index property of the Recordset object, you must know and specify the actual index name.

See Also For information about the various types of Recordset objects and how to use them, see Chapter 5, “Working with Records and Fields.”

You can delete an index from a table by using the Delete method on the Indexes collection of a TableDef object. In the following example, strDbPath is the path to the database, strTableName is the name of the table containing the index, and strIndex is the name of the index:

Dim dbs As Database
Dim tdf As TableDef

Set dbs = OpenDatabase(strDbPath)
Set tdf = dbs.TableDefs(strTableName)
tdf.Indexes.Delete strIndex

Note For performance reasons, you can’t create more than thirty-two indexes on a table in a Microsoft Jet database.