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:
- Create the Index object using the TableDef's CreateIndex method.
- Set the index's properties as appropriate.
- Create the index's Field objects using its CreateField method.
- Append each Field object to the index's Fields collection.
- 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.
In this tutorial:
- Using DAO to Access Data
- Data Access Objects
- New Features in DAO
- Referring to DAO Objects
- The DBEngine Object
- Using Transactions
- The Errors Collection
- The Databases Collection
- The CurrentDb() Function
- Opening an External Database
- Closing and Destroying Database Object References
- DAO Property Types
- Setting and Retrieving Built-In Object Properties
- Setting and Retrieving SummaryInfo Properties
- Creating Schema Objects with DAO
- Creating Indexes
- Creating Relations
- Creating Multi-Value Lookup Fields
- Database Encryption with DAO
- Setting Encryption Options
- Managing Access (JET) Security with DAO
- Creating Security Objects
- Creating and Deleting Groups
- Managing Passwords
- Data Access with DAO
- Modifying a QueryDef
- Filtering and Ordering Recordsets
- Navigating Recordsets
- BOF, EOF
- Navigating Recordsets with Multi-Value Lookup Fields
- Bookmarks and Recordset Clones
- Finding Records
- Working with Recordsets
- Using Arrays with Recordsets
- Working with Attachment Fields
- Append Only Fields