473,706 Members | 6,828 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to Export access BLOBs (images) to files

3 New Member
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
5 16355
bhodgins
3 New Member
Am i asking the wrong question in the wrong forum?
Jul 25 '07 #2
FishVal
2,653 Recognized Expert Specialist
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
2,653 Recognized Expert Specialist
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
bhodgins
3 New Member
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
2,653 Recognized Expert Specialist
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

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

Similar topics

1
3657
by: Raaijmakers, Vincent (GE Infrastructure) | last post by:
It is the first time that I use blobs in mysql. Please help me out here..... Using MSSQLdb and python 2.3.4 I was surprised to see how my information was stored in the blob. My goal is to store JPG images in the blob. Well, it stores it but as a string of bytes: 'x0dx0fxffxa3......' So, when reading back that information into my python environment, it seems that I need to translate the value back to the original binary format? ...
1
4913
by: gary.scott | last post by:
Aaaaaarrgghh ! (that's better) I am trying to convert a field within my Oracle 9i Database that is of type BLOB (but this BLOB may contain a combination of clobs/varchars or images such as gif images, jpg images) to Microsoft SQL Server 2000 using Microsoft DTS. On trying to perform this simple conversion I recieved the error "Need to run the object to perform the operation - Exception Access Violation" from Microsoft DTS and my table...
7
6946
by: Howard Lowndes | last post by:
My situation is that I am interacting PHP 4.1.2 to PostgreSQL 7.2.2 I have no difficulty inserting and managing BLOBs into the Large Object system table, and I have a user table called images which maintains the relationship between the BLOB loid and the identity that relates to it in my user tables. So far so good. When I RTFM obout psql it refers to the \lo_import, \lo_list, \lo_export and \lo_unlink functions.
16
11865
by: David Lauberts | last post by:
Hi Wonder if someone has some words of wisdom. I have a access 2002 form that contains 2 graph objects that overlay each other and would like to export them as a JPEG to use in a presentation. I can do this individually for each graph but this does not help me as I need both on the same JPEG. I thought I would try an export the form that contains both but I am having trouble. (My VBA is self taught and a little knowledge is...
6
6293
by: Chris Cox | last post by:
I'm trying to put together a simple access database that will allow a friend to maintain a simple database of products/pictures/prices, which he can then export to html and upload to a website. Problem is that access doesn't export the images when saving to HTML :( The export to PDF solution isn't appropriate in this instance, is their a third party print to html utility anywhere, or any other easy solution to this problem?
0
1313
by: .Net Newbie | last post by:
I am currently working with the C# version of the PortalSDK in a text editor and have run across a bug that I need to resolve in order for my app to function properly. Basically, I'm using the Documents section to load Blobs into the Portal Database on SQL Server. When I upload small binary documents everything works fine. However, when I try to upload larger files with the same content/mime types as the smaller files I get a response...
0
1295
by: .Net Newbie | last post by:
Basically, I'm using the Documents section of the PortalCSSDK to load Blobs into the Portal Database on SQL Server. When I upload small binary documents everything works fine. However, when I try to upload larger files with the same content/mime types as the smaller files I get a response from the Webserver stating that the page can not be found. I do not know the limit of the file sizes (I know it choked on an 8 MG File) but I have...
3
3474
by: meyvn77 | last post by:
Hello - I am looking for the best way to store images in a Access DB. My Idea - I have a table with 150,000 records. These recoreds represent a Crash (Traffic Accident). I have 50 different images that represent almost any type of Accident. Each crash record can be represented by one of those 50 images (depending on the Crash Data). I don't want to waste space by having each record have a BLOB field w/
10
6930
by: Jerry | last post by:
Hello! I have a SQL Server database with gif images stored in a nText field as binary. I've been asked to export these images to actual gif files (about 250 of them). I found a stored procedure that was using some ADO and it would write a small part of the image correctly and then the rest was a mess. I was told to check out the ADO and VB groups to see if anyone knows of a solution for this. Does anyone have any resources or examples...
0
8693
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9281
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9044
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8986
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7899
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6613
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4444
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3141
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2087
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.