Adding a Single-Field Index to an Existing Table (Intrinsic constants for the IndexNulls property of the ADOX Index object) : Index « Access « VBA / Excel / Access / Word






  1. VBA / Excel / Access / Word
  2. Access
  3. Index

Adding a Single-Field Index to an Existing Table (Intrinsic constants for the IndexNulls property of the ADOX Index object)

 
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("java2sTable")

   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 'java2sTable' 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








Related examples in the same category

1.Auto-Generate an Index Using VBA
2.Adding a Multiple-Field Index to an Existing Table
3.Listing Indexes in a Table
4.Deleting Indexes from a Table
5.Adding a Unique Index Based on Two Fields to an Existing Table
6.Deleting a Field that is a Part of an Index with SQL command
7.Alter table to delete an Index with SQL command
8.Creating an Index that Disallows Null Values in the Key with SQL command
9.Creating an Index with the Ignore Null Option with SQL command
10.Deleting an Index with SQL command
11.Creating a Primary Key Index with Restrictions with SQL command