Share via

Field2.FieldSize Property

  • Article
Access Developer Reference

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

Syntax

expression.FieldSize

expression   A variable that represents a Field2 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 Field2 object other than Memo and Long Binary types, use the Size property.

  • 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).

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.

Visual Basic for Applications 
  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.

Visual Basic for Applications 
  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 Field2, _
fldDestination As Field2)


' 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