IndexNulls Property Example (VB)

This example demonstrates the IndexNulls property of an Index. The code creates a new index and sets the value of IndexNulls based on user input (from a list box named List1). Then, the Index is appended to the Employees Table in the Northwind Catalog. The new Index is applied to a Recordset based on the Employees table, and the Recordset is opened. A new record is added to the Employees table, with a Null value in the indexed field. Whether this new record is displayed depends on the setting of the IndexNulls property.

' BeginIndexNullsVB
Private Sub cmdIndexNulls_Click()
    IndexNullsX
End Sub

Sub IndexNullsX()

    Dim cnn As New ADODB.Connection
    Dim catNorthwind As New ADOX.Catalog
    Dim idxNew As New ADOX.Index
    Dim rstEmployees As New ADODB.Recordset
    Dim varBookmark As Variant
           
    ' Connect the catalog.
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "data source=c:\Program Files\" & _
        "Microsoft Office\Office\Samples\Northwind.mdb;"

    Set catNorthwind.ActiveConnection = cnn
    
    ' Append Country column to new index
    idxNew.Columns.Append "Country"
    idxNew.Name = "NewIndex"
        
    ' Set IndexNulls based on user selection in listbox List1
    Select Case List1.List(List1.ListIndex)
        Case "Allow"
            idxNew.IndexNulls = adIndexNullsAllow
        Case "Ignore"
            idxNew.IndexNulls = adIndexNullsIgnore
        Case Else
            End
    End Select
    
    'Append new index to Employees table
    catNorthwind.Tables("Employees").Indexes.Append idxNew
    
    rstEmployees.Index = idxNew.Name
    rstEmployees.Open "Employees", cnn, adOpenKeyset, _
        adLockOptimistic, adCmdTableDirect
        
    With rstEmployees
        ' Add a new record to the Employees table.
        .AddNew
        !FirstName = "Gary"
        !LastName = "Haarsager"
        .Update
        
        ' Bookmark the newly added record
        varBookmark = .Bookmark

        ' Use the new index to set the order of the records.
        .MoveFirst

        Debug.Print "Index = " & .Index & _
            ", IndexNulls = " & idxNew.IndexNulls
        Debug.Print "  Country - Name"

        ' Enumerate the Recordset. The value of the
        ' IndexNulls property will determine if the newly
        ' added record appears in the output.
        Do While Not .EOF
            Debug.Print "    " & _
                IIf(IsNull(!Country), "[Null]", !Country) & _
                " - " & !FirstName & " " & !LastName
            .MoveNext
        Loop

        ' Delete new record because this is a demonstration.
        .Bookmark = varBookmark
        .Delete
        
        .Close
    End With

    ' Delete new Index because this is a demonstration.
    catNorthwind.Tables("Employees").Indexes.Delete idxNew.Name
    Set catNorthwind = Nothing

End Sub
' EndIndexNullsVB

IndexNulls Property Example (VB)

See Also