Creating Indexes

The basic procedure for creating an index is as follows:

1. Create a Catalog object and define its ActiveConnection property.

2. Create a Table object and instantiate it.

3. Create an Index object.

4. Check if the primary key already exists, and if so, delete it.

5. Create the index using the New keyword, and set its attributes as appropriate.

6. Append the index's columns to the Columns collection.

7. Append the index to the table's Indexes collection.

Remember three things when creating indexes in ADO. First, not all providers support all index attributes. Check the provider's documentation for those it does support. Second, Jet databases do not support clustered indexes. Third, 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 for Jet databases, and PK_tablename for SQL Server databases. Therefore, to maintain consistency, it is wise to give code-created primary keys the same name.

Let's create the primary key. The following sub creates a primary key index for the specified table, which can include multiple fields whose names are supplied in the ParamArray argument. In the case of our invoice tables, there will be only one field in each.

Public Sub CreatePKIndexes(strTableName As String, _ ParamArray varPKColumns() As Variant) Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim idx As ADOX.Index Dim varColumn As Variant

'Create and connect the Catalog object Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection Set tbl = cat.Tables(strTableName)

'Check if a Primary Key exists. If so, delete it. For Each idx In tbl.Indexes If idx.PrimaryKey Then tbl.Indexes.Delete idx.Name End If Next idx

'Create a new primary key Set idx = New ADOX.Index With idx

.Name = "PrimaryKey" .PrimaryKey = True .Unique = True End With

At this point, the index exists in memory, and will remain so until it is added to the table's Indexes collection. But before you do that, you must add the columns that make up the key to the index's Columns collection and refresh the collection.

'Append the columns

For Each varColumn In varPKColumns idx.Columns.Append varColumn Next varColumn

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

'Clean up

Set cat.ActiveConnection = Nothing Set cat = Nothing Set tbl = Nothing Set idx = Nothing End Sub

You should run the CreatePKIndexes procedure to define the indexes for both tblInvoice and tblInvItem tables.

Finally, relationships must be set up between the two tables.

0 0

Post a comment