Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/103257

From BetaArchive Wiki
Knowledge Base


ACC: Reading, Storing, & Writing Binary Large Objects (BLOBs)

Article ID: 103257

Article Last Modified on 1/18/2007



APPLIES TO

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q103257

Advanced: Requires expert coding, interoperability, and multiuser skills.


SUMMARY

You can store large data objects (such as sound, video, or graphic data) in a field with the OLE Object data type in a Microsoft Access table. Some large binary data objects cannot be represented, however, if they do not have an OLE server that understands the data being stored. You can also store copies of executable program files or other non-OLE data in a Microsoft Access table. This type of data is referred to as a binary large object bitmap (BLOB).

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

MORE INFORMATION

The following example contains two sample user-defined functions that you can use to manage large amounts of binary data in a field with the OLE Object data type. The user-defined functions are ReadBLOB() and WriteBLOB().

  • The ReadBLOB() function reads a binary disk file and stores it in an OLE Object field.
  • The WriteBLOB() function writes binary data stored in an OLE Object field to a disk file.

This example demonstrates how to copy a binary file into an OLE Object field, and then how to write it back out to a new disk file:

  1. Create a new module called BLOB and enter the following lines in the module's Declarations section:

    Option Explicit
    Const BlockSize = 32768

    NOTE: If you are working with Microsoft Access 2.0, you will need to include the following definitions after the two previous ones:

          Const dbOpenTable = DB_OPEN_TABLE
          Const acSysCmdInitMeter = SYSCMD_INITMETER
          Const acSysCmdUpdateMeter = SYSCMD_UPDATEMETER
          Const acSysCmdRemoveMeter = SYSCMD_REMOVEMETER
  2. Enter the following code in the module.

    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

    NOTE: The following technique will not work for Microsoft Access 1.x. For Microsoft Access 1.x, you will need to modify the code so that it uses Table variables instead of Recordset variables and the OpenTable function instead of OpenRecordset.

          '**************************************************************
          ' FUNCTION: ReadBLOB()
          '
          ' PURPOSE:
          '   Reads a BLOB from a disk file and stores the contents in the
          '   specified table and field.
          '
          ' PREREQUISITES:
          '   The specified table with the OLE object field to contain the
          '   binary data must be opened in Visual Basic code (Access Basic
          '   code in Microsoft Access 2.0 and earlier) and the correct record
          '   navigated to prior to calling the ReadBLOB() function.
          '
          ' ARGUMENTS:
          '   Source - The path and filename of the binary information
          '            to be read and stored.
          '   T      - The table object to store the data in.
          '   Field  - The OLE object field in table T to store the data in.
          '
          ' RETURN:
          '   The number of bytes read from the Source file.
          '**************************************************************
          Function ReadBLOB(Source As String, T As Recordset, _
          sField As String)
              Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
              Dim FileLength As Long, LeftOver As Long
              Dim FileData As String
              Dim RetVal As Variant
    
              On Error GoTo Err_ReadBLOB
    
              ' Open the source file.
              SourceFile = FreeFile
              Open Source For Binary Access Read As SourceFile
    
              ' Get the length of the file.
              FileLength = LOF(SourceFile)
              If FileLength = 0 Then
                  ReadBLOB = 0
                  Exit Function
              End If
    
              ' Calculate the number of blocks to read and leftover bytes.
              NumBlocks = FileLength \ BlockSize
              LeftOver = FileLength Mod BlockSize
    
              ' SysCmd is used to manipulate status bar meter.
              RetVal = SysCmd(acSysCmdInitMeter, "Reading BLOB", _
                       FileLength \ 1000)
    
              ' Put the record in edit mode.
              T.Edit
    
              ' Read the leftover data, writing it to the table.
              FileData = String$(LeftOver, 32)
              Get SourceFile, , FileData
              T(sField).AppendChunk (FileData)
    
              RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
    
              ' Read the remaining blocks of data, writing them to the table.
              FileData = String$(BlockSize, 32)
              For i = 1 To NumBlocks
                  Get SourceFile, , FileData
                  T(sField).AppendChunk (FileData)
    
                  RetVal = SysCmd(acSysCmdUpdateMeter, BlockSize * i / 1000)
              Next i
    
              ' Update the record and terminate function.
              T.Update
              RetVal = SysCmd(acSysCmdRemoveMeter)
              Close SourceFile
              ReadBLOB = FileLength
              Exit Function
    
          Err_ReadBLOB:
              ReadBLOB = -Err
              Exit Function
    
          End Function
    
          '**************************************************************
          ' FUNCTION: WriteBLOB()
          '
          ' PURPOSE:
          '   Writes BLOB information stored in the specified table and field
          '   to the specified disk file.
          '
          ' PREREQUISITES:
          '   The specified table with the OLE object field containing the
          '   binary data must be opened in Visual Basic code (Access Basic
          '   code in Microsoft Access 2.0 or earlier) and the correct
          '   record navigated to prior to calling the WriteBLOB() function.
          '
          ' ARGUMENTS:
          '   T           - The table object containing the binary information.
          '   sField      - The OLE object field in table T containing the
          '                 binary information to write.
          '   Destination - The path and filename to write the binary
          '                 information to.
          '
          ' RETURN:
          '   The number of bytes written to the destination file.
          '**************************************************************
          Function WriteBLOB(T As Recordset, sField As String, _
          Destination As String)
              Dim NumBlocks As Integer, DestFile As Integer, i As Integer
              Dim FileLength As Long, LeftOver As Long
              Dim FileData As String
              Dim RetVal As Variant
    
              On Error GoTo Err_WriteBLOB
    
              ' Get the size of the field.
              FileLength = T(sField).FieldSize()
              If FileLength = 0 Then
                  WriteBLOB = 0
                  Exit Function
              End If
    
              ' Calculate number of blocks to write and leftover bytes.
              NumBlocks = FileLength \ BlockSize
              LeftOver = FileLength Mod BlockSize
    
              ' Remove any existing destination file.
              DestFile = FreeFile
              Open Destination For Output As DestFile
              Close DestFile
    
              ' Open the destination file.
              Open Destination For Binary As DestFile
    
              ' SysCmd is used to manipulate the status bar meter.
              RetVal = SysCmd(acSysCmdInitMeter, _
              "Writing BLOB", FileLength / 1000)
    
              ' Write the leftover data to the output file.
              FileData = T(sField).GetChunk(0, LeftOver)
              Put DestFile, , FileData
    
              ' Update the status bar meter.
              RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
    
              ' Write the remaining blocks of data to the output file.
              For i = 1 To NumBlocks
                  ' Reads a chunk and writes it to output file.
                  FileData = T(sField).GetChunk((i - 1) * BlockSize _
                     + LeftOver, BlockSize)
                  Put DestFile, , FileData
    
                  RetVal = SysCmd(acSysCmdUpdateMeter, _
                  ((i - 1) * BlockSize + LeftOver) / 1000)
              Next i
    
              ' Terminates function
              RetVal = SysCmd(acSysCmdRemoveMeter)
              Close DestFile
              WriteBLOB = FileLength
              Exit Function
    
          Err_WriteBLOB:
              WriteBLOB = -Err
              Exit Function
    
          End Function
    
          '**************************************************************
          ' SUB: CopyFile
          '
          ' PURPOSE:
          '   Demonstrates how to use ReadBLOB() and WriteBLOB().
          '
          ' PREREQUISITES:
          '   A table called BLOB that contains an OLE Object field called
          '   Blob.
          '
          ' ARGUMENTS:
          '   Source - The path and filename of the information to copy.
          '   Destination - The path and filename of the file to write
          '                 the binary information to.
          '
          ' EXAMPLE:
          '   CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"
          '**************************************************************
          Sub CopyFile(Source As String, Destination As String)
              Dim BytesRead As Variant, BytesWritten As Variant
              Dim Msg As String
              Dim db As Database
              Dim T As Recordset
    
              ' Open the BLOB table.
              Set db = CurrentDb()
              Set T = db.OpenRecordset("BLOB", dbOpenTable)
    
              ' Create a new record and move to it.
              T.AddNew
              T.Update
              T.MoveLast
    
              BytesRead = ReadBLOB(Source, T, "Blob")
    
              Msg = "Finished reading """ & Source & """"
              Msg = Msg & Chr$(13) & ".. " & BytesRead & " bytes read."
              MsgBox Msg, 64, "Copy File"
    
              BytesWritten = WriteBLOB(T, "Blob", Destination)
    
              Msg = "Finished writing """ & Destination & """"
              Msg = Msg & Chr$(13) & ".. " & BytesWritten & " bytes written."
              MsgBox Msg, 64, "Copy File"
          End Sub
                            
  3. Create the following new table, and then save it as BLOB:

          Table: BLOB
          ------------------------
          Field Name: Blob
             Data Type: OLE Object
  4. With the BLOB module open in Design view, click Debug Window (or Immediate Window in Microsoft Access 2.0 or earlier) on the View menu.
  5. Type the following line in the Debug window, and then press ENTER:

          CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"

The ReadBLOB() and WriteBLOB() functions copy the Microsoft Windows Help file to the Blob field in the BLOB table, and then from there to a disk file called Winfil_1.hlp

Keywords: kbinfo kbprogramming KB103257