Creating Indexes

Just creating the tables and fields isn't enough. Eventually the invoices tables are going to get pretty big, so querying against them will begin to take some time. The bigger the table,the longer it takes. To provide some measure of performance, you need to create indexes, because without proper indexes, the Jet engine must scan the entire table to find the records you want. The basic procedure for creating an index is as follows:

1. Create the Index object using the TableDef's CreateIndex method.

2. Set the index's attributes 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, there are three things you should be aware of. First, once an index has been 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.

Second, although you can give an index any name you like, when you create a primary key using the Access Table Designer, it will be automatically named PrimaryKey. Therefore, to maintain consistency, it is wise to give code-created primary keys the same name.

Third, Jet databases do not support clustered indexes, so in Jet workspaces and other workspaces that connect to databases that use the Jet engine, the Index object's Clustered property is ignored.

Let's 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 our invoice tables, that'll be only one field in each.

Public Sub CreatePKIndexes(strTableName As String, _ ParamArray varPKFields() As Variant) Dim dbs As Database Dim tdf As DAO.TableDef Dim idx As DAO.Index Dim idxFld As Variant Dim varPKey As Variant

Set dbs = CurrentDb On Error Resume Next

Set tdf = dbs.TableDefs(strTableName)

'Check if a Primary Key exists. 'If so, delete it. varPKey = GetPrimaryKey(tdf) If Not IsNull(varPKey) 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 will remain 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 Each idxFld In varPKFields

Set idxFld = idx.CreateField(idxFld) idx.Fields.Append idxFld Next idxFld

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

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 Variant 'Determine if the specified Primary Key exists Dim idx As Variant

For Each idx In tdf.Indexes If idx.Primary Then

'If a Primary Key exists, return its name GetPrimaryKey = idx.Name GoTo GetPrimaryKey_Exit End If Next idx

'If no Primary Key exists, return Null GetPrimaryKey = Null

GetPrimaryKey_Exit: End Function

You should 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, since Access is a relational database, you should set up relationships between the two tables, to tell Access how the information in one table relates to information in the other. This allows you to create related datasets in queries. The following section describes how to create those relationships in code.

Was this article helpful?

0 0

Post a comment