DefinedSize Property Example (VB)

DefinedSize Property Example (VB)

This example demonstrates the DefinedSize property of a Column. The code will redefine the size of the FirstName column of the Employees table of the Northwind database. Then, the change in the values of the FirstName Field of a Recordset based on the Employees table is displayed. Note that by default, the FirstName field becomes padded with spaces after you redefine the DefinedSize property.

Public Sub DefinedSizeX()

    Dim rstEmployees As ADODB.Recordset
    Dim catNorthwind As New ADOX.Catalog
    Dim colFirstName As ADOX.Column
    Dim colNewFirstName As New ADOX.Column
    Dim aryFirstName() As String
    Dim i As Integer
    Dim strCnn As String

    strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=c:\Program Files\" & _
             "Microsoft Office\Office\Samples\Northwind.mdb;"

    ' Open a Recordset for the Employees table.
    Set rstEmployees = New ADODB.Recordset
    rstEmployees.Open "Employees", strCnn, adOpenKeyset, , adCmdTable
    ReDim aryFirstName(rstEmployees.RecordCount)
    
    ' Open a Catalog for the Northwind database,
    ' using same connection as rstEmployees
    Set catNorthwind.ActiveConnection = rstEmployees.ActiveConnection
    
    ' Loop through the recordset displaying the contents
    ' of the FirstName field, the field's defined size,
    ' and its actual size.
    ' Also store FirstName values in aryFirstName array.
    rstEmployees.MoveFirst
    Debug.Print " "
    Debug.Print "Original Defined Size and Actual Size"
    i = 0
    Do Until rstEmployees.EOF
        Debug.Print "Employee name: " & rstEmployees!FirstName & _
            " " & rstEmployees!LastName
        Debug.Print "    FirstName Defined size: " _
            & rstEmployees!FirstName.DefinedSize
        Debug.Print "    FirstName Actual size: " & _
            rstEmployees!FirstName.ActualSize
            aryFirstName(i) = rstEmployees!FirstName
        rstEmployees.MoveNext
        i = i + 1
    Loop
    rstEmployees.Close
 
    ' Redefine the DefinedSize of FirstName in the catalog
    Set colFirstName = catNorthwind.Tables("Employees").Columns("FirstName")
    colNewFirstName.Name = colFirstName.Name
    colNewFirstName.Type = colFirstName.Type
    colNewFirstName.DefinedSize = colFirstName.DefinedSize + 1
    
    ' Append new FirstName column to catalog
    catNorthwind.Tables("Employees").Columns.Delete colFirstName.Name
    catNorthwind.Tables("Employees").Columns.Append colNewFirstName

    ' Open Employee table in Recordset for updating
    rstEmployees.Open "Employees", catNorthwind.ActiveConnection, _
        adOpenKeyset, adLockOptimistic, adCmdTable
    
    ' Loop through the recordset displaying the contents
    ' of the FirstName field, the field's defined size,
    ' and its actual size.
    ' Also restore FirstName values from aryFirstName.
    rstEmployees.MoveFirst
    Debug.Print " "
    Debug.Print "New Defined Size and Actual Size"
    i = 0
    Do Until rstEmployees.EOF
        rstEmployees!FirstName = aryFirstName(i)
        Debug.Print "Employee name: " & rstEmployees!FirstName & _
            " " & rstEmployees!LastName
        Debug.Print "    FirstName Defined size: " _
            & rstEmployees!FirstName.DefinedSize
        Debug.Print "    FirstName Actual size: " & _
            rstEmployees!FirstName.ActualSize
        rstEmployees.MoveNext
        i = i + 1
    Loop
    rstEmployees.Close
    
    ' Restore original FirstName column to catalog
    catNorthwind.Tables("Employees").Columns.Delete colNewFirstName.Name
    catNorthwind.Tables("Employees").Columns.Append colFirstName
    
    ' Restore original FirstName values to Employees table
    rstEmployees.Open "Employees", catNorthwind.ActiveConnection, _
        adOpenKeyset, adLockOptimistic, adCmdTable
    
    rstEmployees.MoveFirst
    i = 0
    Do Until rstEmployees.EOF
        rstEmployees!FirstName = aryFirstName(i)
        rstEmployees.MoveNext
        i = i + 1
    Loop
    rstEmployees.Close
        
    Set catNorthwind = Nothing

End Sub