ADOX: Jet Data Definition in ADO - Access Database Design and Programming - page 326

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim idx As New ADOX.Index

' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\temp\ADOXExample.mdb;"

Set tbl = cat.Tables("Newtable")

' Create Index object and append table column to it
idx.Name = "Newindex"
idx.Columns.Append "Column1"

' Allow Null values
idx.IndexNulls = adIndexNullsAllow

' Append the Index object to the table's Indexes collection
tbl.Indexes.Append idx

End Sub

The DAO Index object has two properties that determine the behavior of Nulls within an
index: Required and IgnoreNulls. Both of these properties are False by default, implying
that Null values are allowed in the index and that an index entry is added for each row
with a Null value in the index field.

On the other hand, ADO has a single property, called IndexNulls, that governs the
behavior of Null values in indexes. By default, the IndexNullsproperty is set to
adIndexNullsDisallow , implying that Null values are not allowed in the index and that
no index entry will be added if a field contains Null. Table 18-3 compares the relevant
settings in DAO and ADOX.

Table 18-3. Comparison of constants for treating nulls

DAO
IgnoreNulls

DAORequired ADOX IndexNulls Description

Null value not allowed in index field; no index
entry added

True False adIndexNullsDisallow

Null value allowed in index field; no index entry
added

False True adIndexNullsIgnore

Null value allowed in index field; index entry
added

False False adIndexNullsAllow

18.1.5 Creating a Primary Key

In DAO, primary keys are created by setting the Primary property of the Index object to
True . In ADOX, we proceed as follows:

1. Create a Key object.
2. Set its Type property to Primary using the adKeyPrimary constant.