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.