Attributes Property Example (VB)

Attributes Property Example (VB)

This example demonstrates the Attributes property of a Column. Setting it to adColNullable allows the user to set the value of a Recordset Field to an empty string. In this situation, the user can distinguish between a record where data is not known and a record where the data does not apply.

Sub AttributesX()

    Dim cnn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim colTemp As New ADOX.Column
    Dim rstEmployees As New Recordset
    Dim strMessage As String
    Dim strInput As String
        
    ' Connect the catalog.
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=c:\" & _
        "Program Files\Microsoft Office\Office\Samples\Northwind.mdb;"
    Set cat.ActiveConnection = cnn

    Set tblEmp = cat.Tables("Employees")
    
    ' Create a new Field object and append it to the Fields
    ' collection of the Employees table.
    colTemp.Name = "FaxPhone"
    colTemp.Type = adVarWChar
    colTemp.DefinedSize = 24
    colTemp.Attributes = adColNullable
    cat.Tables("Employees").Columns.Append colTemp
    
    ' Open the Employees table for updating as a Recordset
    rstEmployees.Open "Employees", cnn, adOpenKeyset, adLockOptimistic
    
    With rstEmployees
        ' Get user input.
        strMessage = "Enter fax number for " & _
            !FirstName & " " & !LastName & "." & vbCr & _
            "[? - unknown, X - has no fax]"
        strInput = UCase(InputBox(strMessage))
        If strInput <> "" Then
            Select Case strInput
                Case "?"
                    !FaxPhone = Null
                Case "X"
                    !FaxPhone = ""
                Case Else
                    !FaxPhone = strInput
            End Select
            .Update
            
            ' Print report.
            Debug.Print "Name - Fax number"
            Debug.Print !FirstName & " " & !LastName & " - ";

            If IsNull(!FaxPhone) Then
                Debug.Print "[Unknown]"
            Else
                If !FaxPhone = "" Then
                    Debug.Print "[Has no fax]"
                Else
                    Debug.Print !FaxPhone
                End If
            End If

        End If

        .Close
    End With

    ' Delete new field because this is a demonstration.
    tblEmp.Columns.Delete colTemp.Name
    cnn.Close
    
End Sub