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.

' BeginDefinedSizeVB
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
' EndDefinedSizeVB

See Also

Column Object | DefinedSize Property

© 1998-2001 Microsoft Corporation. All rights reserved.