IndexNulls プロパティの例 (VB)

この例では、Index の IndexNulls プロパティの機能を示します。このコードでは、新規インデックスを作成し、ユーザーが List1 という名前のリスト ボックスに入力した内容に基づいて IndexNulls の値を設定します。Index は、Northwind Catalog の Employees Table に追加されます。新規 Index が Employees テーブルに基づいて Recordset に適用され、Recordset が開かれます。新規レコードが Employees テーブルに追加され、インデックス フィールドに Null 値が入ります。この新規レコードが表示されるかどうかは、IndexNulls プロパティの設定によって決まります。

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