DAO Object

Size

 
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.
 

Syntax

expression .FieldSize
expression A variable that represents a Field object.
 

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.
 
The FieldSize property and the VBA Len() or LenB() functions may return different values as the length of the same string. Strings are stored in a Microsoft Access database in multi-byte character set (MBCS) form, but exposed through VBA in Unicode format. As a result, the Len() function will always return the number of characters, LenB will always return the number of characters X 2 (Unicode uses two bytes for each character), but FieldSize will return some value in between if the string has any MBCS characters.
For example, given a string consisting of three normal characters and two MBCS characters, Len() will return 5, LenB() will return 10, and FieldSize will return 7, the sum of 1 for each normal character and 2 for each MBCS character.
 

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
 
This example uses the AppendChunk and GetChunk methods to fill an OLE object field with data from another record, 32K at a time. In a real application, one might use a procedure like this to copy an employee record (including the employee's photo) from one table to another. In this example, the record is simply being copied back to same table. Note that all the chunk manipulation takes place within a single AddNew-Update sequence.
 
Sub AppendChunkX()
    
     Dim dbsNorthwind As Database
     Dim rstEmployees As Recordset
     Dim rstEmployees2 As Recordset
    
     Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    
     ' Open two recordsets from the Employees table.
     Set rstEmployees = _
     dbsNorthwind.OpenRecordset("Employees", _
     dbOpenDynaset)
     Set rstEmployees2 = rstEmployees.Clone
    
     ' Add a new record to the first Recordset and copy the
     ' data from a record in the second Recordset.
     With rstEmployees
     .AddNew
     !FirstName = rstEmployees2!FirstName
     !LastName = rstEmployees2!LastName
     CopyLargeField rstEmployees2!Photo, !Photo
     .Update
    
     ' Delete new record because this is a demonstration.
     .Bookmark = .LastModified
     .Delete
     .Close
     End With
    
     rstEmployees2.Close
     dbsNorthwind.Close
    
    End Sub
    
    Function CopyLargeField(fldSource As Field, _
     fldDestination As Field)
    
     ' Set size of chunk in bytes.
     Const conChunkSize = 32768
    
     Dim lngOffset As Long
     Dim lngTotalSize As Long
     Dim strChunk As String
    
     ' Copy the photo from one Recordset to the other in 32K
     ' chunks until the entire field is copied.
     lngTotalSize = fldSource.FieldSize
     Do While lngOffset < lngTotalSize
     strChunk = fldSource.GetChunk(lngOffset, conChunkSize)
     fldDestination.AppendChunk strChunk
     lngOffset = lngOffset + conChunkSize
     Loop
    
    End Function