Large Text and Binary Data Fields

Two kinds of fields in a Microsoft Jet database are used to store lengthy values. Pictures, OLE objects, or whole files can be stored in an OLE object field as binary data. An OLE object field is also called a Binary Large OBject (BLOB) field. A Memo field stores text data only. Memo fields are sometimes called BLOB fields, too, but that is somewhat misleading. A Memo field is merely a variable-length text field. Both fields are handled similarly.

Large variable-length fields like these must be handled differently than ordinary fixed-length fields, such as integer and date fields. Typically, a variant, a string, or a dynamic byte array is used to read or update the value of the field. However, sometimes the length of the data stored in the field will be too large to store at once in a variant. Breaking the data into smaller, manageable chunks reduces the memory that must be allocated to work with these values. The GetChunk and AppendChunk methods of the Field objects in DAO and ADO break up the data in these fields into smaller chunks.

The following listings demonstrate how to use GetChunk to read large values from a memo field.

DAO

Sub DAOReadMemo()

   Dim db As DAO.Database

   Dim rst As DAO.Recordset

   Dim sNotes As String

   Dim sChunk As String

   Dim cchChunkReceived As Long

   Dim cchChunkRequested As Long

   Dim cchChunkOffset As Long

   ' Open the database

   Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")

   ' Open the Recordset

   Set rst = db.OpenRecordset _

      ("SELECT Notes FROM Employees", dbOpenDynaset)

   ' Initialize offset

   cchChunkOffset = 0

   ' cchChunkRequested artifically set low at 16

   ' to demonstrate looping

   cchChunkRequested = 16

   ' Loop through as many chunks as it takes

   ' to read the entire BLOB into memory

   Do

      ' Temporarily store the next chunk

      sChunk = rst!Fields("Notes").GetChunk _

         (cchChunkOffset, cchChunkRequested)

      ' Check how much we got

      cchChunkReceived = Len(sChunk)

      ' Adjust offset for next iteration

      cchChunkOffset = cchChunkOffset + cchChunkReceived

      ' If we got anything,

      ' concatenate it to the main BLOB

      If cchChunkReceived > 0 Then

         sNotes = sNotes & sChunk

      End If

   Loop While cchChunkReceived = cchChunkRequested

   ' For this example, print the value of

   ' the Notes field for just the first record

   Debug.Print sNotes

   ' Close the recordset

   rst.Close

End Sub

ADO

Sub ADOReadMemo()

   Dim cnn As New ADODB.Connection

   Dim rst As New ADODB.Recordset

   Dim sNotes As String

   Dim sChunk As String

   Dim cchChunkReceived As Long

   Dim cchChunkRequested As Long

   ' Open the connection

   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

      "Data Source=.\NorthWind.mdb;"

   ' Open the recordset

   rst.Open "SELECT Notes FROM Employees ", _

      cnn, adOpenKeyset, adLockOptimistic

   ' cchChunkRequested artifically set low at 16

   ' to demonstrate looping

   cchChunkRequested = 16

   ' Loop through as many chunks as it takes

   ' to read the entire BLOB into memory

   Do

      ' Temporarily store the next chunk

      sChunk = rst.Fields("Notes").GetChunk(cchChunkRequested)

      ' Check how much we got

      cchChunkReceived = Len(sChunk)

      ' If we got anything,

      ' concatenate it to the main BLOB

      If cchChunkReceived > 0 Then

         sNotes = sNotes & sChunk

      End If

   Loop While cchChunkReceived = cchChunkRequested

   ' For this example, print the value of

   ' the Notes field for just the first record

   Debug.Print sNotes

   ' Close the recordset

   rst.Close

End Sub

The code to use GetChunk and AppendChunk in ADO is similar to the code in DAO. In the DAO example, the offset to read the next chunk of data from the field must be explicitly calculated and given as a parameter to the GetChunk method. In ADO, the Recordset stores the offset for consecutive GetChunk calls on the same field automatically. Any clones of the Recordset share this offset. This offset is automatically incremented as you call call GetChunk consecutively on a single field. Reading from a different field in the Recordset or moving the Recordset to a different record will reset the offset to the beginning of the field.

The following listings demonstrate how to update binary data in an OLE object field without using the GetChunk or AppendChunk methods.

DAO

Sub DAOUpdateBLOB()

   Dim db As DAO.Database

   Dim rst As DAO.Recordset

   Dim rgPhoto() As Byte

   ' Open the database

   Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")

   ' Open the Recordset

   Set rst = db.OpenRecordset( _

      "SELECT Photo FROM Employees", dbOpenDynaset)

   ' Get the first photo

   rgPhoto = rst.Fields("Photo").Value

   ' Move to the next record

   rst.MoveNext

   ' Put the Recordset in Edit Mode

   rst.Edit

   ' Copy the photo into the next record

   rst.Fields("Photo").Value = rgPhoto

   ' Save the changes you made to the

   ' current record in the Recordset

   rst.Update

   ' Close the recordset

   rst.Close

End Sub

ADO

Sub ADOUpdateBLOB()

   Dim cnn As New ADODB.Connection

   Dim rst As New ADODB.Recordset

   Dim rgPhoto() As Byte

   ' Open the connection

   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

      "Data Source=.\NorthWind.mdb;"

   ' Open the recordset

   rst.Open "SELECT Photo FROM Employees ", _

      cnn, adOpenKeyset, adLockOptimistic

   ' Get the first photo

   rgPhoto = rst.Fields("Photo").Value

   ' Move to the next record

   rst.MoveNext

   ' Copy the photo into the next record

   rst.Fields("Photo").Value = rgPhoto

   ' Save the changes you made to the

   ' current record in the Recordset

   rst.Update

   ' Close the recordset

   rst.Close

End Sub

The code for updating binary data without using GetChunk or AppendChunk is almost identical to the code for updating ordinary fields such as text, numeric, or date fields. The only difference is that it uses a dynamic byte array to store the values. The previous ADO code  shows the same differences from DAO as was shown earlier in the example for Updating a Record.