Microsoft KB Archive/255632

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

Article ID: 255632

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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
 Open the sample project NorthwindCS.adp. On the Tools menu, point to Database Utilities, and then click Make ADE File. 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.  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   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  Save the module, and then on the View menu, click Immediate Window.</li>  Type the following line in the Immediate window, and then press ENTER: subCopyFile "C:\My Documents\NorthwindCS.ade", _ "C:\My Documents\NorthwindCS_2.ade" </li></ol>

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.

Keywords: kbinfo kbprogramming KB255632

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.