| Microsoft Office 2000/Visual Basic Programmer's Guide | |
The process for creating an index is essentially the same in ADOX as it is in DAO. To create an index, you append fields to an Index object's Columns collection, and then append the Index object to the Table object's Indexes collection.
However, there are differences in behavior between the Index objects in these two models. DAO has two properties, Required and IgnoreNulls, that together determine whether or not Null values can be inserted for fields in the index, and whether or not index entries will be created when some of the fields in a multicolumn index contain Nulls. By default, both of these properties are False, indicating that Null values are allowed in the index, and that an index entry will be added even if the entry contains Nulls. This differs from ADOX, which has a single property, IndexNulls, for this purpose. By default, the IndexNulls property is set to adIndexNullsDisallow, which indicates that Null values aren't allowed in the index, and that no index entry will be added if a field in the index contains a Null. The table below shows the mapping from the DAO Required and IgnoreNulls properties to the ADOX IndexNulls property.
| DAO Required and IgnoreNulls settings | ADO IndexNulls settings | Result |
| Required = True IgnoreNulls =True or False |
adIndexNullsDisallow | A Null value isn't allowed in the index field; no index entry added. |
| Required = False IgnoreNulls = True |
adIndexNullsIgnore | A Null value is allowed in the index field; but no index entry added. |
| Required = False IgnoreNulls = False |
adIndexNullsAllow | A Null value is allowed in the index field; index entry added. |
Note ADOX also provides the adIndexNullsAllowAny constant for setting the IndexNulls property, but this setting returns an error if you try to use it to create an index in an Access database.
The following code sample shows how to create an index by using ADOX.
Sub CreateIndex(strDbPath As String, _
strTblToIdx As String, _
strIdxName As String, _
strIdxField As String, _
lngIndexNulls As ADOX.AllowNullsEnum, _
lngSortOrder As ADOX.SortOrderEnum)
Dim catDB As ADOX.Catalog
Dim tbl As ADOX.Table
Dim idx As ADOX.Index
Set catDB = New ADOX.Catalog
' Open a catalog on the database in which to create the index.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDbPath
Set tbl = New ADOX.Table
Set tbl = catDB.Tables(strTblToIdx)
' Create Index object and append table columns to it.
Set idx = New ADOX.Index
With idx
.Name = strIdxName
.IndexNulls = lngIndexNulls
.Columns.Append strIdxField
.Columns(strIdxField).SortOrder = lngSortOrder
End With
' Append the Index object to the Indexes collection of the Table object.
tbl.Indexes.Append idx
Set catDB = Nothing
End Sub
For example, to use the CreateIndex procedure to create an index on the Country field in the Employees table that is sorted in ascending order and allows nulls, you can use a line of code like this:
CreateIndex _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
"Employees", "CountryIndex", "Country", _
"adIndexNullsIgnore","adSortAscending"
The same process can be used to create an index that will used as the table's primary key. To do this, set the PrimaryKey property of the Index object to True before appending it to the table's Indexes collection.
The CreateIndex procedure can be found in the CreateDatabase module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.