Creating Indexes

Just creating the tables and fields isn't enough. Eventually the tables are going to get pretty big, and querying against them will take some time. To provide some measure of performance, you need to create indexes because without proper indexes, the Access engine must scan the entire table to find the records you want. Here's the basic procedure for creating an index:

1. Create the Index object using the TableDef's CreateIndex method.
2. Set the index's properties as appropriate.
3. Create the index's Field objects using its CreateField method.
4. Append each Field object to the index's Fields collection.
5. Append the index to the TableDef's Indexes collection.

Before you create your first index, you should be aware of the following three things:

• Once an index is appended to its collection, its properties are read-only. Therefore, if you want to change an index's property after you've created it, you must delete the index and re-create it with the new properties.
• Although you can give an index any name you like, when you create a primary key using the Access Table Designer, it is automatically named PrimaryKey. To maintain consistency, it is wise to give code-created primary keys the same name.
• Access databases do not support clustered indexes, so in Access workspaces and other workspaces that connect to databases that use the Access database engine, the Index object's Clustered property is ignored.

Start the process of creating indexes by creating the primary key. When you create a primary key, Access automatically creates an index for it. The following procedure creates a primary key index for the specified table, which includes the fields supplied in the ParamArray argument. In the case of the invoice tables, that'll be only one field in each.

Public Sub CreatePKIndexes(strTableName As String, ParamArray varPKFields())
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index
    Dim fld As DAO.Field
    Dim strPKey As String
    Dim strIdxFldName As String
    Dim intCounter As Integer

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTableName)

    'Check if a Primary Key exists.
    'If so, delete it.
    strPKey = GetPrimaryKey(tdf)

    If Len(strPKey) > 0 Then
	tdf.Indexes.Delete varPKey
    End If

    'Create a new primary key
    Set idx = tdf.CreateIndex("PrimaryKey")
    idx.Primary = True
    idx.Required = True
    idx.Unique = True

At this point, the index exists in memory, and remains so until it is added to the TableDef's Indexes collection. But before you do that, you must add the fields that make up the key to the index's Fields collection, and refresh the collection.

    'Append the fields
    For intCouter = LBound(varPKFields) To UBound(varPKFields)
	' get the field name
	strIdxFldName = varPKFields(intCounter)

	' get the field object and append it to the index
	Set fld = idx.CreateField(strIdxFldName)
	idx.Fields.Append fld
    Next intCounter

    'Append the index to the Indexes collection
    tdf.Indexes.Append idx

    'Refresh the Indexes collection
    tdf.Indexes.Refresh

    Set fld = Nothing
    Set idx = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
End Sub

The following function is called from the above CreatePKIndexes procedure, and returns the name of the primary key if one exists, and Null if there isn't one:

Public Function GetPrimaryKey(tdf As DAO.TableDef) As String
    'Determine if the specified Primary Key exists
    Dim idx As DAO.Index

    For Each idx In tdf.Indexes
	If idx.Primary Then
	    'If a Primary Key exists, return its name
	    GetPrimaryKey = idx.Name
	    Exit Function
	End If
    Next idx

    'If no Primary Key exists, return empty string
    GetPrimaryKey = vbNullString
End Function

Run the CreatePKIndexes procedure to define the indexes for both the tblInvoice and tblInvItem tables. In fact, you can run this procedure in your own applications to create indexes on any table that doesn't have primary keys defined. Finally, because Access is a relational database, set up relationships between the two tables to tell Access how the information in one table relates to information in the other. This enables you to create related datasets in queries. The following section describes how to create those relationships in code.