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


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q255632
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

SUMMARY

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

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 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.
Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbinfo kbProgramming KB255632
©2004 Microsoft Corporation. All rights reserved.