Mega Code Archive
Adding a Single-Field Index to an Existing Table (Intrinsic constants for the IndexNulls property of the ADOX Index object)
<![CDATA[
Constant Name Description
adIndexNullsAllow create an index if there is a Null value in the index field (an error will not occur).
adIndexNullsDisallow (default value)You cannot create an index if there is a Null in the index for the column (an error will occur).
adIndexNullsIgnore create an index if there is a Null in the index field (an error will not occur).
adIndexNullsIgnoreAny You can create an index if there is a Null value in the index field.
Sub Add_SingleFieldIndex()
Dim cat As New ADOX.Catalog
Dim myTable As New ADOX.Table
Dim myIndex As New ADOX.Index
On Error GoTo ErrorHandler
cat.ActiveConnection = CurrentProject.Connection
Set myTable = cat.Tables("rntsoftTable")
With myIndex
.Name = "idxDescription"
.Unique = False
.IndexNulls = adIndexNullsIgnore
.Columns.Append "Description"
.Columns(0).SortOrder = adSortAscending
End With
myTable.Indexes.Append myIndex
Set cat = Nothing
Exit Sub
ErrorHandler:
If Err.Number = -2147217856 Then
MsgBox "The 'rntsoftTable' cannot be open.", vbCritical, _
"Close the table"
ElseIf Err.Number = -2147217868 Then
myTable.Indexes.Delete myIndex.Name
Resume 0
Else
MsgBox Err.Number & ": " & Err.Description
End If
End Sub
]]>