Size Property

Applies To Field object.

Description

Sets or returns a value that indicates the maximum size, in bytes, of a Field object.

Settings and Return Values

The setting or return value is a constant that indicates the maximum size of a Field object. For an object not yet appended to the Fields collection, this property is read/write. The setting depends on the Type property setting of the Field object, as discussed under Remarks.

Remarks

For fields (other than Memo type fields) that contain character data, the Size property indicates the maximum number of characters that the field can hold. For numeric fields, the Size property indicates how many bytes of storage are required.

Use of the Size property depends on the object that contains the Fields collection to which the Field object is appended, as shown in the following table.

Object appended to	Usage

Index	Not supported
QueryDef	Read-only
Recordset	Read-only
Relation	Not supported
TableDef	Read-only

When you create a Field object with a data type other than Text, the Type property setting automatically determines the Size property setting; you don't need to set it. For a Field object with the Text data type, however, you can set Size to any integer up to the maximum text size (255 for Microsoft Jet databases). If you do not set the size, the field will be as large as the database allows.

For Long Binary and Memo Field objects, Size is always set to 0. Use the FieldSize property of the Field object to determine the size of the data in a specific record. The maximum size of a Long Binary or Memo field is limited only by your system resources or the maximum size that the database allows.

See Also Attributes property, Index object, Type property.

Specifics (Microsoft Access)

The Size property is equivalent to the FieldSize property in table Design view. You can't set the FieldSize property by using Visual Basic; you must use the Size property instead.

In Microsoft Access, the Memo data type field can contain up to 1.2 gigabytes of information. However, Microsoft Access only displays the first 32 kilobytes of data in a control on a form or report.

Example

This example demonstrates the Size property by enumerating the names and sizes of the Field objects in the Employees table.

Sub SizeX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim fldNew As Field
    Dim fldLoop As Field

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set tdfEmployees = dbsNorthwind.TableDefs!Employees

    With tdfEmployees

        ' Create and append a new Field object to the
        ' Employees table.
        Set fldNew = .CreateField("FaxPhone")
        fldNew.Type = dbText
        fldNew.Size = 20
        .Fields.Append fldNew

        Debug.Print "TableDef: " & .Name
        Debug.Print "    Field.Name - Field.Type - Field.Size"

        ' Enumerate Fields collection; print field names,
        ' types, and sizes.
        For Each fldLoop In .Fields
            Debug.Print "        " & fldLoop.Name & " - " & _
                fldLoop.Type & " - " & fldLoop.Size
        Next fldLoop

        ' Delete new field because this is a demonstration.
        .Fields.Delete fldNew.Name

    End With

    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example creates a new Field object and sets its Size and Type properties. The procedure then appends the new object to the Fields collection of the Employees table in the TableDefs collection of the database.

Sub NewField()
    Dim dbs As Database, tdf As TableDef
    Dim fld As Field

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Employees table.
    Set tdf = dbs.TableDefs!Employees
    ' Create Field object.
    Set fld = tdf.CreateField("DaysOfVacation")
    ' Set field properties.
    fld.Type = dbText
    fld.Size = 20
    ' Append fld to Fields collection.
    tdf.Fields.Append fld
    Set dbs = Nothing
End Sub

You can also use the CreateField method to set the Name, Type, and Size properties if you provide name, type, and size as arguments.

Set fld = tdfEmployees.CreateField("DaysOfVacation", dbText, 20)

Example (Microsoft Excel)

This example copies to Sheet1 all fields of the Double type from Orddtail.dbf, a dBASE IV table located in the C:\Program Files\Common Files\Microsoft Shared
\MSquery folder. (In Windows NT, Orddtail.dbf is located in the C:\Windows\Msapps\Msquery folder.)

Dim db As Database, recordsToCopy As Recordset, tDef As Recordset
Dim fieldsToStore(1000), fileName As String
fileName = "ORDDTAIL.DBF"
Set db = _
Workspaces(0).OpenDatabase("C:\Program Files\Common Files" _
    & "\Microsoft Shared\MSquery", False, False, "dBASE IV")
Set tDef = db.OpenRecordset(fileName)
n = 0
Sheets("Sheet1").Activate
For i = 0 To tDef.Fields.Count - 1
    If tDef.Fields(i).Type = dbDouble Then
        fieldsToStore(n) = tDef.fields(i).Name
        n = n + 1
    End If
Next
If fieldsToStore(0) = "" Then
    MsgBox "There are no number fields in this table."
    Exit Sub
End If
For i = 0 To n - 1
    records = "SELECT " & "[" & fieldsToStore(i) & "]" _
        & " from " & db.Recordsets(fileName).Name & ";"
    Set recordsToCopy = db.OpenRecordset(records)
    With ActiveSheet.Cells(1, i + 1)
        .CopyFromRecordset recordsToCopy
        .ColumnWidth = recordsToCopy.fields(0).Size
    End With
Next
recordsToCopy.Close
tDef.Close
db.Close