How to Export access BLOBs (images) to files

[bhodgins]

Hi,
I am new on here, and had a newbie question that I am stumped with.
I am not new to access, but am new to VB.
I am trying to export BLOBs from a field called photo to external jpeg files. I have tried the MS kb 210486 and successfully got the import/export to work with a sample table, but only the first record. I do not wish to impost binary data to the database, it's already there. I simply wish to pick up the binary BLOB from the photo field and export it to an image file, autonaming it.

Here is a sample of my table.

IDnumber | Lastname | Firstname | Address | Photo |
----------------------------------------------------------------------------
12485881 | Simpson | Homer...... |12 Hills St| long binary data |
12335682 | Fernando | Manny.......| 1 Elm St | long binary data |...

I am trying to take all the BLOBs under the "photo" field and export them to jpgs using the IDnumber field as the filename.

I have cut the code down to the following as I do not need to import an image in, it is already there. After cutting it down this far, I am getting an ambiguous name: Copyfile.

Option Explicit
Const BlockSize = 32768
 
'**************************************************************
' 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(Destination As String)
    Dim BytesWritten As Variant
    Dim Msg As String
    Dim db As DAO.Database
    Dim T As DAO.Recordset
 
    ' Open the University table.
    Set db = CurrentDb()
    Set T = db.OpenRecordset("University", dbOpenTable)
 
    ' Create a new record and move to it.
    T.AddNew
    T.Update
    T.MoveLast
 
 
    BytesWritten = WriteBLOB(T, "Photo", Destination)
 
    Msg = "Finished writing """ & Destination & """"
    Msg = Msg & Chr$(13) & ".. " & BytesWritten & " bytes written."
    MsgBox Msg, 64, "Copy File"
End Sub
 

Using Access 2003, the db format is Access 2000.

[bhodgins]

Am i asking the wrong question in the wrong forum?

[FishVal]

Hi,
I am new on here, and had a newbie question that I am stumped with.
I am not new to access, but am new to VB.
I am trying to export BLOBs from a field called photo to external jpeg files. I have tried the MS kb 210486 and successfully got the import/export to work with a sample table, but only the first record. I do not wish to impost binary data to the database, it's already there. I simply wish to pick up the binary BLOB from the photo field and export it to an image file, autonaming it.

Here is a sample of my table.

IDnumber | Lastname | Firstname | Address | Photo |
----------------------------------------------------------------------------
12485881 | Simpson | Homer...... |12 Hills St| long binary data |
12335682 | Fernando | Manny.......| 1 Elm St | long binary data |...

I am trying to take all the BLOBs under the "photo" field and export them to jpgs using the IDnumber field as the filename.

I have cut the code down to the following as I do not need to import an image in, it is already there. After cutting it down this far, I am getting an ambiguous name: Copyfile.

Option Explicit
Const BlockSize = 32768
 
'**************************************************************
' 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(Destination As String)
    Dim BytesWritten As Variant
    Dim Msg As String
    Dim db As DAO.Database
    Dim T As DAO.Recordset
 
    ' Open the University table.
    Set db = CurrentDb()
    Set T = db.OpenRecordset("University", dbOpenTable)
 
    ' Create a new record and move to it.
    T.AddNew
    T.Update
    T.MoveLast
 
 
    BytesWritten = WriteBLOB(T, "Photo", Destination)
 
    Msg = "Finished writing """ & Destination & """"
    Msg = Msg & Chr$(13) & ".. " & BytesWritten & " bytes written."
    MsgBox Msg, 64, "Copy File"
End Sub
 

Using Access 2003, the db format is Access 2000.

Hi.

I'm pretty not sure that content of an OLE field is the same as that of file you've inserted to the field, unless you've inserted it with AppendChunk method.
Common sence. Access needs to store OLE wrapper together with raw data to open a record properly.

[FishVal]

Hi.

I'm pretty not sure that content of an OLE field is the same as that of file you've inserted to the field, unless you've inserted it with AppendChunk method.
Common sence. Access needs to store OLE wrapper together with raw data to open a record properly.

[bhodgins]

I'm not understanding. I am using the sample code from MS, and trying to work it so that I can write an existing BLOB to a file, rather than import one first.

[FishVal]

I'm not understanding. I am using the sample code from MS, and trying to work it so that I can write an existing BLOB to a file, rather than import one first.

Try to decrease BlockSize. I'm not sure that String type variable can contain 32k of data.
I've tested a similar code. It worked fine with BlockSize 512 bytes.