By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,362 Members | 3,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,362 IT Pros & Developers. It's quick & easy.

How to Export access BLOBs (images) to files

P: 3
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.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Const BlockSize = 32768
  3.  
  4. '**************************************************************
  5. ' FUNCTION: WriteBLOB()
  6. '
  7. ' PURPOSE:
  8. '   Writes BLOB information stored in the specified table and field
  9. '   to the specified disk file.
  10. '
  11. ' PREREQUISITES:
  12. '   The specified table with the OLE object field containing the
  13. '   binary data must be opened in Visual Basic code and the correct
  14. '   record navigated to prior to calling the WriteBLOB() function.
  15. '
  16. ' ARGUMENTS:
  17. '   T           - The table object containing the binary information.
  18. '   sField      - The OLE object field in table T containing the
  19. '                 binary information to write.
  20. '   Destination - The path and filename to write the binary
  21. '                 information to.
  22. '
  23. ' RETURN:
  24. '   The number of bytes written to the destination file.
  25. '**************************************************************
  26. Function WriteBLOB(T As DAO.Recordset, sField As String, _
  27. Destination As String)
  28.     Dim NumBlocks As Integer, DestFile As Integer, i As Integer
  29.     Dim FileLength As Long, LeftOver As Long
  30.     Dim FileData As String
  31.     Dim RetVal As Variant
  32.  
  33.     On Error GoTo Err_WriteBLOB
  34.  
  35.     ' Get the size of the field.
  36.     FileLength = T(sField).FieldSize()
  37.     If FileLength = 0 Then
  38.         WriteBLOB = 0
  39.         Exit Function
  40.     End If
  41.  
  42.     ' Calculate number of blocks to write and leftover bytes.
  43.     NumBlocks = FileLength \ BlockSize
  44.     LeftOver = FileLength Mod BlockSize
  45.  
  46.     ' Remove any existing destination file.
  47.     DestFile = FreeFile
  48.     Open Destination For Output As DestFile
  49.     Close DestFile
  50.  
  51.     ' Open the destination file.
  52.     Open Destination For Binary As DestFile
  53.  
  54.     ' SysCmd is used to manipulate the status bar meter.
  55.     RetVal = SysCmd(acSysCmdInitMeter, _
  56.     "Writing BLOB", FileLength / 1000)
  57.  
  58.     ' Write the leftover data to the output file.
  59.     FileData = T(sField).GetChunk(0, LeftOver)
  60.     Put DestFile, , FileData
  61.  
  62.     ' Update the status bar meter.
  63.     RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
  64.  
  65.     ' Write the remaining blocks of data to the output file.
  66.     For i = 1 To NumBlocks
  67.         ' Reads a chunk and writes it to output file.
  68.         FileData = T(sField).GetChunk((i - 1) * BlockSize _
  69.            + LeftOver, BlockSize)
  70.         Put DestFile, , FileData
  71.  
  72.         RetVal = SysCmd(acSysCmdUpdateMeter, _
  73.         ((i - 1) * BlockSize + LeftOver) / 1000)
  74.     Next i
  75.  
  76.     ' Terminates function
  77.     RetVal = SysCmd(acSysCmdRemoveMeter)
  78.     Close DestFile
  79.     WriteBLOB = FileLength
  80.     Exit Function
  81.  
  82. Err_WriteBLOB:
  83.     WriteBLOB = -Err
  84.     Exit Function
  85.  
  86. End Function
  87.  
  88. '**************************************************************
  89. ' SUB: CopyFile
  90. '
  91. ' PURPOSE:
  92. '   Demonstrates how to use ReadBLOB() and WriteBLOB().
  93. '
  94. ' PREREQUISITES:
  95. '   A table called BLOB that contains an OLE Object field called
  96. '   Blob.
  97. '
  98. ' ARGUMENTS:
  99. '   Source - The path and filename of the information to copy.
  100. '   Destination - The path and filename of the file to write
  101. '                 the binary information to.
  102. '
  103. ' EXAMPLE:
  104. '   CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"
  105. '**************************************************************
  106. Sub CopyFile(Destination As String)
  107.     Dim BytesWritten As Variant
  108.     Dim Msg As String
  109.     Dim db As DAO.Database
  110.     Dim T As DAO.Recordset
  111.  
  112.     ' Open the University table.
  113.     Set db = CurrentDb()
  114.     Set T = db.OpenRecordset("University", dbOpenTable)
  115.  
  116.     ' Create a new record and move to it.
  117.     T.AddNew
  118.     T.Update
  119.     T.MoveLast
  120.  
  121.  
  122.     BytesWritten = WriteBLOB(T, "Photo", Destination)
  123.  
  124.     Msg = "Finished writing """ & Destination & """"
  125.     Msg = Msg & Chr$(13) & ".. " & BytesWritten & " bytes written."
  126.     MsgBox Msg, 64, "Copy File"
  127. End Sub
  128.  
Using Access 2003, the db format is Access 2000.
Jul 19 '07 #1
Share this Question
Share on Google+
5 Replies


P: 3
Am i asking the wrong question in the wrong forum?
Jul 25 '07 #2

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Const BlockSize = 32768
  3.  
  4. '**************************************************************
  5. ' FUNCTION: WriteBLOB()
  6. '
  7. ' PURPOSE:
  8. '   Writes BLOB information stored in the specified table and field
  9. '   to the specified disk file.
  10. '
  11. ' PREREQUISITES:
  12. '   The specified table with the OLE object field containing the
  13. '   binary data must be opened in Visual Basic code and the correct
  14. '   record navigated to prior to calling the WriteBLOB() function.
  15. '
  16. ' ARGUMENTS:
  17. '   T           - The table object containing the binary information.
  18. '   sField      - The OLE object field in table T containing the
  19. '                 binary information to write.
  20. '   Destination - The path and filename to write the binary
  21. '                 information to.
  22. '
  23. ' RETURN:
  24. '   The number of bytes written to the destination file.
  25. '**************************************************************
  26. Function WriteBLOB(T As DAO.Recordset, sField As String, _
  27. Destination As String)
  28.     Dim NumBlocks As Integer, DestFile As Integer, i As Integer
  29.     Dim FileLength As Long, LeftOver As Long
  30.     Dim FileData As String
  31.     Dim RetVal As Variant
  32.  
  33.     On Error GoTo Err_WriteBLOB
  34.  
  35.     ' Get the size of the field.
  36.     FileLength = T(sField).FieldSize()
  37.     If FileLength = 0 Then
  38.         WriteBLOB = 0
  39.         Exit Function
  40.     End If
  41.  
  42.     ' Calculate number of blocks to write and leftover bytes.
  43.     NumBlocks = FileLength \ BlockSize
  44.     LeftOver = FileLength Mod BlockSize
  45.  
  46.     ' Remove any existing destination file.
  47.     DestFile = FreeFile
  48.     Open Destination For Output As DestFile
  49.     Close DestFile
  50.  
  51.     ' Open the destination file.
  52.     Open Destination For Binary As DestFile
  53.  
  54.     ' SysCmd is used to manipulate the status bar meter.
  55.     RetVal = SysCmd(acSysCmdInitMeter, _
  56.     "Writing BLOB", FileLength / 1000)
  57.  
  58.     ' Write the leftover data to the output file.
  59.     FileData = T(sField).GetChunk(0, LeftOver)
  60.     Put DestFile, , FileData
  61.  
  62.     ' Update the status bar meter.
  63.     RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
  64.  
  65.     ' Write the remaining blocks of data to the output file.
  66.     For i = 1 To NumBlocks
  67.         ' Reads a chunk and writes it to output file.
  68.         FileData = T(sField).GetChunk((i - 1) * BlockSize _
  69.            + LeftOver, BlockSize)
  70.         Put DestFile, , FileData
  71.  
  72.         RetVal = SysCmd(acSysCmdUpdateMeter, _
  73.         ((i - 1) * BlockSize + LeftOver) / 1000)
  74.     Next i
  75.  
  76.     ' Terminates function
  77.     RetVal = SysCmd(acSysCmdRemoveMeter)
  78.     Close DestFile
  79.     WriteBLOB = FileLength
  80.     Exit Function
  81.  
  82. Err_WriteBLOB:
  83.     WriteBLOB = -Err
  84.     Exit Function
  85.  
  86. End Function
  87.  
  88. '**************************************************************
  89. ' SUB: CopyFile
  90. '
  91. ' PURPOSE:
  92. '   Demonstrates how to use ReadBLOB() and WriteBLOB().
  93. '
  94. ' PREREQUISITES:
  95. '   A table called BLOB that contains an OLE Object field called
  96. '   Blob.
  97. '
  98. ' ARGUMENTS:
  99. '   Source - The path and filename of the information to copy.
  100. '   Destination - The path and filename of the file to write
  101. '                 the binary information to.
  102. '
  103. ' EXAMPLE:
  104. '   CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"
  105. '**************************************************************
  106. Sub CopyFile(Destination As String)
  107.     Dim BytesWritten As Variant
  108.     Dim Msg As String
  109.     Dim db As DAO.Database
  110.     Dim T As DAO.Recordset
  111.  
  112.     ' Open the University table.
  113.     Set db = CurrentDb()
  114.     Set T = db.OpenRecordset("University", dbOpenTable)
  115.  
  116.     ' Create a new record and move to it.
  117.     T.AddNew
  118.     T.Update
  119.     T.MoveLast
  120.  
  121.  
  122.     BytesWritten = WriteBLOB(T, "Photo", Destination)
  123.  
  124.     Msg = "Finished writing """ & Destination & """"
  125.     Msg = Msg & Chr$(13) & ".. " & BytesWritten & " bytes written."
  126.     MsgBox Msg, 64, "Copy File"
  127. End Sub
  128.  
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.
Jul 25 '07 #3

FishVal
Expert 2.5K+
P: 2,653
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.
Jul 25 '07 #4

P: 3
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.
Jul 31 '07 #5

FishVal
Expert 2.5K+
P: 2,653
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.
Jul 31 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.