FieldSize Property

Applies To Field object.

Description

Returns the number of bytes used in the database (rather than in memory) of a Memo or Long Binary Field object in the Fields collection of a Recordset object.

Return Values

The return value is a Long that indicates the number of characters (for a Memo field) or the number of bytes (for a Long Binary field).

Remarks

You can use FieldSize with the AppendChunk and GetChunk methods to manipulate large fields.

Because the size of a Long Binary or Memo field can exceed 64K, you should assign the value returned by FieldSize to a variable large enough to store a Long variable.

To determine the size of a Field object other than Memo and Long Binary types, use the Size property.

Note In an ODBCDirect workspace, the FieldSize property is not available in the following situations:

If the database server or ODBC driver does not support server-side cursors.
If you are using the ODBC cursor library (that is, the DefaultCursorDriver property is set to dbUseODBC, or to dbUseDefault when the server does not support server-side cursors).
If you are using a cursorless query (that is, the DefaultCursorDriver property is set to dbUseNoCursor).

For example, Microsoft SQL Server version 4.21 does not support server-side cursors, so the FieldSize property is not available.

See Also AppendChunk method, GetChunk method, Size property, Type property.

Specifics (Microsoft Access)

The DAO FieldSize property is different from the Microsoft Access FieldSize property, which is set in table Design view. The DAO FieldSize property returns the number of bytes in a Field object of type Memo or OLE Object.

You set the Microsoft Access FieldSize property in order to limit the size of a field in a table. The FieldSize property is only available in table Design view. From Visual Basic, use the Size property to set the size of a field in a table.

Example

This example uses the FieldSize property to list the number of bytes used by the Memo and Long Binary Field objects in two different tables.

Sub FieldSizeX()

    Dim dbsNorthwind As Database
    Dim rstCategories As Recordset
    Dim rstEmployees As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstCategories = _
        dbsNorthwind.OpenRecordset("Categories", _
        dbOpenDynaset)
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees", _
        dbOpenDynaset)

    Debug.Print _
        "Field sizes from records in Categories table"

    With rstCategories
        Debug.Print "    CategoryName - " & _
            "Description (bytes) - Picture (bytes)"

    ' Enumerate the Categories Recordset and print the size
    ' in bytes of the picture field for each record.
        Do While Not .EOF
            Debug.Print "        " & !CategoryName & " - " & _
                !Description.FieldSize & " - " & _
                !Picture.FieldSize
            .MoveNext
        Loop

        .Close
    End With

    Debug.Print "Field sizes from records in Employees table"

    With rstEmployees
        Debug.Print "    LastName - Notes (bytes) - " & _
            "Photo (bytes)"

    ' Enumerate the Employees Recordset and print the size
    ' in bytes of the picture field for each record.
        Do While Not .EOF
            Debug.Print "        " & !LastName & " - " & _
                !Notes.FieldSize & " - " & !Photo.FieldSize
            .MoveNext
        Loop

        .Close
    End With

    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example uses the FieldSize property to return the size in bytes of two fields in an Employees table. The Notes field contains Memo data and the Photo field contains Long Binary (OLE Object) data.

Sub GetFieldSize()
    Dim dbs As Database, rst As Recordset
    Dim fldNotes As Field, fldPhoto As Field
    Dim strSQL As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Construct SQL statement to return Notes and Photo fields.
    strSQL = "SELECT Notes, Photo FROM Employees;"
    ' Create dynaset-type Recordset object.
    Set rst = dbs.OpenRecordset(strSQL)
    Set fldNotes = rst!Notes
    Set fldPhoto = rst!Photo
    ' Move to first record.
    rst.MoveFirst
    Debug.Print "Size of Notes:"; "    "; "Size of Photo:"
    ' Print sizes of fields for each record in Recordset object.
    Do Until rst.EOF
        Debug.Print fldNotes.FieldSize; "        "; _
            fldPhoto.FieldSize
        rst.MoveNext
    Loop
    rst.Close
    Set dbs = Nothing
End Sub