Article ID: 210486
Article Last Modified on 10/11/2006
APPLIES TO
- Microsoft Access 2000 Standard Edition
This article was previously published under Q210486
This article applies only to a Microsoft Access database (.mdb).
Advanced: Requires expert coding, interoperability, and multiuser skills.
SUMMARY
You can store large data objects (such as sound, video, or graphics 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 an Access table. This type of data is referred to as a binary large object bitmap (BLOB).
MORE INFORMATION
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. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.
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(), which do the following:
- 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:
Create a new module called BLOB and type or paste the following lines in the module's Declarations section:
Option Explicit Const BlockSize = 32768
Type or paste the following code in the module.
'************************************************************** ' 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 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 DAO.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 first record in edit mode. T.MoveFirst 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 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 DAO.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 DAO.Database Dim T As DAO.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
Create the following new table, and then save it as BLOB:
Table: BLOB --------------------- Field Name: Blob Data Type: OLE Object
- With the BLOB module open in Design view, click Immediate Window on the View menu.
- If you are using Windows 95, type the following line in the Immediate window, and then press ENTER:
CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"
If you are using Windows 98, type the following line in the Immediate window, and then press ENTER:
CopyFile "c:\windows\help\winfile.hlp", "c:\winfil_1.hlp"
If you are using Windows NT 4.0, type the following line in the Immediate window, and then press ENTER:
CopyFile "c:\WINNT\SYSTEM32\winfile.hlp", "c:\winfil_1.hlp"
The ReadBLOB() and WriteBLOB() functions copy the 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 KB210486