ACC2000: How to Read, Store, and Write BLOBs to SQL Server Tables

In a Microsoft Access project that is bound to Microsoft SQL Server 7.0 tables, you can store large data objects (such as sound, video, or graphical data) in a field that has the Image data type. Some large binary data objects cannot be represented, however, if they do not have an OLE Server that understands the data that is being stored. You can also store copies of executable program files or other non-OLE data. This type of data is referred to as a Binary Large Object Bitmap (BLOB).

The following example contains two sample user-defined functions that you can use to manage large amounts of binary data in a field that has the Image data type. The user-defined functions are fncReadBLOB() and fncWriteBLOB().

NOTE: The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run properly, you must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later.) To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. This example demonstrates how to copy a binary file into an Image field, and then how to write it back out to a new disk file as follows:

• The fncReadBLOB() function reads a binary file and stores it in an Image field.
• The fncWriteBLOB() function writes binary data stored in an Image field to a disk file.

Example

1. Open the sample project NorthwindCS.adp.
2. On the Tools menu, point to Database Utilities, and then click Make ADE File.
3. In the Save ADE As dialog box, browse to the C:\My Documents folder, and click Save. This saves the NorthwindCS.ade file in the C:\My Documents folder.
4. In NorthwindCS.adp project, create the following table, and then save it with the name tblBlob:

      Table: tblBlob
      -----------------------
      Column Name: PK
      Data Type: Int
      Allow Nulls: False
      Identity: True
   
      Column Name: Source
      Data Type: text
      Allow Nulls: True
      Identity: False
   
      Column Name: Destination
      Data Type: text
      Allow Nulls: True
      Identity: False
   
      Column Name: Blob
      Data Type: image
      Allow Nulls: True
      Identity: False
   
      Table Properties: tblBlob
      -------------------------------
      PrimaryKey: PK
      Index1: Category Name; Location
   					

5. Create a new module and name modReadWriteBLOB. Type or paste the following code into the new module:

   Option Compare Database
   Option Explicit
   
   Const BLOCKSIZE = 32768
   
   '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   'FUNCTION: fncReadBLOB()
   '
   'PURPOSE:
   '   Reads a BLOB from a file and stores it in specified table and field.
   '
   'PREREQUISITES:
   '   Table with the Image field to contain the binary data must
   '   be opened using Visual Basic for Applications code and the correct
   '   record navigated to, prior to calling the fncReadBLOB() function.
   '
   'ARGUMENTS:
   '   strSource - Path and filename of external file to be read and stored.
   '   rstTable - The table object to store the data in.
   '   strField - The Image field in table rstTable to store the data in.
   '
   'RETURN:
   '   The number of bytes read from the Source file.
   '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   Function fncReadBLOB(strSource As String, rstTable As ADODB.Recordset, _
       strField As String)
   
       Dim intNumBlocks As Integer, intSourceFile As Integer, intI As Integer
       Dim lngFileLength As Long, lngLeftOver As Long
       Dim strFileData As String
       Dim varRetVal As Variant
   
       On Error GoTo Err_ReadBLOB
   
       'Open the source file.
       intSourceFile = FreeFile
       Open strSource For Binary Access Read As intSourceFile
   
       'Get the length of the file.
       lngFileLength = LOF(intSourceFile)
   
       'File is invalid if length equals zero.
       If lngFileLength = 0 Then
           fncReadBLOB = 0
           Exit Function
       End If
   
       'Calculate the number of blocks to read and the leftover bytes.
       intNumBlocks = lngFileLength \ BLOCKSIZE
       lngLeftOver = lngFileLength Mod BLOCKSIZE
   
       'Read the leftover data, writing it to the table.
       strFileData = String$(lngLeftOver, 32)
       
       'Read data from the external file.
       Get intSourceFile, , strFileData
       
       'Write the data to the Image field.
       rstTable(strField).AppendChunk (strFileData)
   
       'Read the remaining blocks of data, writing them to the table.
       strFileData = String$(BLOCKSIZE, 32)
   
       For intI = 1 To intNumBlocks
           Get intSourceFile, , strFileData
           rstTable(strField).AppendChunk (strFileData)
       Next intI
   
       'Update the record and terminate the function.
       rstTable.Update
       Close intSourceFile
       fncReadBLOB = lngFileLength
       Exit Function
   
   Err_ReadBLOB:
       fncReadBLOB = -Err
       Exit Function
   
   End Function
   
   '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   'FUNCTION: fncWriteBLOB()
   '
   'PURPOSE:
   '   Writes the BLOB stored in table and field to specified disk file.
   '
   'PREREQUISITES:
   '   Table with the Image field containing the binary data must be opened
   '   using Visual Basic for Applications code and the correct record
   '   navigated to prior to calling the fncWriteBLOB() function.
   '
   'ARGUMENTS:
   '   rstTable - The table object containing the binary information.
   '   strField - Image field in table containing binary information to
   '              write.
   '   strDestination - Path and filename to write the binary information to.
   '
   'RETURN:
   '   The number of bytes written to the destination file.
   '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   Function fncWriteBLOB(rstTable As ADODB.Recordset, strField As String, _
       strDestination As String)
   
       Dim intNumBlocks As Integer, intDestFile As Integer, intI As Integer
       Dim lngFileLength As Long, lngLeftOver As Long
       Dim strFileData As String
       Dim varRetVal As Variant
   
       On Error GoTo Err_WriteBLOB
   
       'Get the size of the field.
       lngFileLength = rstTable(strField).ActualSize
   
       'Cancel if field is empty.
       If lngFileLength = 0 Then
           fncWriteBLOB = 0
           Exit Function
       End If
   
       'Calculate number of blocks to write and the leftover bytes.
       intNumBlocks = lngFileLength \ BLOCKSIZE
       lngLeftOver = lngFileLength Mod BLOCKSIZE
   
       'Create pointer for to destination file.
       intDestFile = FreeFile
       Open strDestination For Output As intDestFile
       Close intDestFile
   
       'Open the destination file.
       Open strDestination For Binary As intDestFile
   
       'Write the leftover data to the output file.
       strFileData = rstTable(strField).GetChunk(lngLeftOver)
   
       'Write data to the external file.
       Put intDestFile, , strFileData
   
       'Read the leftover chunks and write it to output file.
       For intI = 1 To intNumBlocks
           strFileData = rstTable(strField).GetChunk((intI - 1) * _ 
             BLOCKSIZE + lngLeftOver)
           Put intDestFile, , strFileData
       Next intI
   
       'Close the external file and terminate the function.
       Close intDestFile
       fncWriteBLOB = lngFileLength
       Exit Function
   
   Err_WriteBLOB:
       If Err.Number = 94 Then
           Resume Next
       Else
           fncWriteBLOB = -Err
           Exit Function
       End If
   End Function
   
   '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   'SUB: subCopyFile
   '
   'PURPOSE:
   '   Demonstrates how to use fncReadBLOB() and fncWriteBLOB().
   '
   'PREREQUISITES:
   '   A table called tblBlob that contains an Image field called Blob.
   '
   'ARGUMENTS:
   '   strSource - The path and filename of the information to copy.
   '   strDestination - Path and filename used when creating output file.
   '
   'EXAMPLE:
   '   subCopyFile "C:\My Documents\NorthwindCS.ade", _
   '               "C:\My Documents\NorthwindCS_2.ade"
   '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   Sub subCopyFile(strSource As String, strDestination As String)
       Dim varBytesRead As Variant, varBytesWritten As Variant
       Dim strMsg As String
       Dim Conn As New ADODB.Connection
       Dim rstTable As New ADODB.Recordset
   
       'Create connection and open the tblBlob table.
       Set Conn = CurrentProject.Connection
       rstTable.Open "tblBlob", Conn, adOpenDynamic, adLockOptimistic
   
       'Create a new record and move to it.
       rstTable.AddNew
       rstTable("Source") = strSource
       rstTable("Destination") = strDestination
       rstTable.Update
   
       'Call the Read Blob function.
       varBytesRead = fncReadBLOB(strSource, rstTable, "Blob")
       strMsg = "Finished reading """ & strSource & """"
       strMsg = strMsg & vbCrLf & varBytesRead & " bytes read."
       
       'Display results after copying external file.
       MsgBox strMsg, vbInformation, "Copy File"
   
       'Call the Write Blob function.
       varBytesWritten = fncWriteBLOB(rstTable, "Blob", strDestination)
       strMsg = "Finished writing """ & strDestination & """"
       strMsg = strMsg & vbCrLf & varBytesWritten & " bytes written."
   
       'Display the results after creating external file.
       MsgBox strMsg, vbInformation, "Write File"
   End Sub
   					

6. Save the module, and then on the View menu, click Immediate Window.
7. Type the following line in the Immediate window, and then press ENTER:

   subCopyFile "C:\My Documents\NorthwindCS.ade", _ 
   "C:\My Documents\NorthwindCS_2.ade"
   					

The fncReadBLOB() and fncWriteBLOB() functions copy the NorthwindCS.ade file to the Image field in tblBLOB, and then from the Image field, write it to an external file called NorthwindCS_2.ade.