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

How to display BLOB images in a MS Access Report?

100+
P: 137
Hi all,

I'm trying to create a report that displays all relevant information per record. However this includes displaying a screenshot for each record.
Images are uploaded and stored as BLOBS. I've used this method as many discussions say it is the best option, due to bloating and path issues with the other methods. However to write the code for this myself is a little outside my knowledge, so I've had to use some found on the internet and alter it accordingly.

Everything regarding uploading the file to the database is working, but my problem now is getting to files to display in a report. I've tried using everything from object frames to activeX image controls, but I fear I do not know enough about what I'm doing here to make use of them correctly. Can anyone please offer any help on how to display BLOB images in reports?

Thanks.

Adam.
Mar 7 '11 #1

✓ answered by ADezii

@Adam - the following Code will extract the contents of a BLOB Field into an ADODB Stream, write the Stream to a Unique File Name in a TEMP Directory under the Current Project Directory based on a Unique ID ([InvID]) and Extension ([sFileExtension]). These Files will then be dynamically loaded into an Image Control in a Report. Pay close attention to Code in the Click() Event of the single Command Button as well as the Format() Event of the Report's Detail Section. Make sure to set a Reference to the Microsoft ActiveX Data Objects X.X Object Library. I'll post the Code, but definitely download the Attachment to see what is going on.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdTest_Click()
  2. On Error GoTo Err_cmdTest_Click
  3. Dim strSQL As String
  4. Dim rstBLOB As ADODB.Recordset
  5. Dim mstream As ADODB.stream
  6. Dim strFullPath As String
  7.  
  8. 'Create a Directory named TEMP if it doesn't exist under the Current Project Path
  9. If Dir$(CurrentProject.Path & "\TEMP\", vbDirectory) = "" Then
  10.   MkDir CurrentProject.Path & "\TEMP\"
  11. End If
  12.  
  13. strSQL = "SELECT tblInventoryPics.* FROM tblInventoryPics"
  14.  
  15. Set rstBLOB = New ADODB.Recordset
  16. rstBLOB.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  17.  
  18. If rstBLOB.RecordCount = 0 Then Exit Sub
  19.  
  20. Set mstream = New ADODB.stream
  21.     mstream.Open
  22.  
  23. With rstBLOB
  24.   Do While Not .EOF
  25.     mstream.Type = adTypeBinary
  26.     mstream.Write rstBLOB.Fields("oPicture").Value          'Write to the Stream Object
  27.  
  28.     'Write BLOB to a File in the TEMP Directory under the Current Project Directory,
  29.     'with a Unique Inventory ID (InvID) and Extension (sFileExtension)
  30.     strFullPath = CurrentProject.Path & "\TEMP\" & ![InvID] & "." & ![sFileExtension]
  31.     mstream.SaveToFile strFullPath, adSaveCreateOverWrite
  32.       .MoveNext         'For each Record
  33.   Loop
  34. End With
  35.  
  36. rstBLOB.Close
  37. Set rstBLOB = Nothing
  38.  
  39. 'Open Report
  40. DoCmd.OpenReport "rptInventory", acViewPreview, , , acWindowNormal
  41. DoCmd.Maximize
  42.  
  43. Exit_cmdTest_Click:
  44.   Exit Sub
  45.  
  46. Err_cmdTest_Click:
  47.   MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
  48.     Resume Exit_cmdTest_Click
  49. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.   Me![imgPicture].Picture = CurrentProject.Path & "\TEMP\" & Me![InvID] & "." & Me![sFileExtension]
  3. End Sub

Share this Question
Share on Google+
23 Replies


ADezii
Expert 5K+
P: 8,627
@Adam - What I am about to propose is strictly Theory on my part, and I have absolutely no idea as to whether or not it will work. Since the BLOB Data is stored in a Binary Format, it cannot be displayed directly on a Report in any manner that I know of. This is what I am proposing:
  1. Extract the Binary Data comprising the BLOB from the OLE Object Field and write it to an ADO Stream Object.
  2. Save the contents of the Stream Object (BLOB) to a Temp File with a Primary Key as a Unique Identifier.
  3. Load the Temp File into the Picture Property of an Image Control for each Record in the Record Source of the Report. This can be done in the Detail Section of the Report.
  4. Before you even attempt this approach, you must know the File Extension associated with each BLOB (.bmp, .jpg, etc.).
  5. If you do know the Extension for each BLOB, then I will be happy to test this Theory for you, just let me know.
Mar 7 '11 #2

100+
P: 137
Well the hope was that it would handle as many file extensions as possible, at least all the major image extensions. From what you've said, do I assume different coding is required for each?

Also, would this method allow for a report to be auto-generated based on a query, with multiple images?
What I'm trying to create is like a "View-All" report that shows every record in one report, but each report includes at least one image, possibly more. Also these records are subject to change, so cannot have any hardcoded file paths.

Only reason I ask now is this methods looks a little over my head, and will clearly take me some time to do, so I want to know if it's worth it. (Of course all knowledge is worth learning, but I need to be specific at the moment :) )

The only extensions currently in the database are .jpg files, as that's what I got hold of first.

Cheers.

Adam.
Mar 8 '11 #3

ADezii
Expert 5K+
P: 8,627
Give me a little time to see if I can come up with anything since this is an area that I never ventured into before. I'll get back to you on this matter.
Mar 8 '11 #4

Rabbit
Expert Mod 10K+
P: 12,359
First off. Why are you storing the image data in the database? Couldn't you have stored it on the file system and then just store the path to the image?

Second, if you don't know what type of image file it is, you need to store the type in another field so that when you extract it, you can append the correct extension. Otherwise, you'll need to look up the header information of the various image encodings to figure out what kind of encoding was used to create the picture.
Mar 8 '11 #5

100+
P: 137
Various review articles I've read have suggested that storing the files as BLOBS is the most efficient way of using images with Access - the other methods appear to have issues such as bloating, and file paths doesn't have the integrity I want, should the original file be removed.
You make a good point about the file extension - while I know what it is, the system doesn't, so I'll go back and add code in for that.

I don't even know if what I'm after is possible. All I really know is where I'm at now, and where I want to be, but none of the methods I've tried have worked.
Mar 8 '11 #6

ADezii
Expert 5K+
P: 8,627
@Adam - Rabbit's point is definitely valid concerning the File Type (Extension) which is exactly why I asked you this in the first place. Shortly, I'll demo some code on how to extract BLOBs to an actual File, then hopefully display these Images in an Image Control in a Report, but again File's Extension is needed.
Mar 8 '11 #7

ADezii
Expert 5K+
P: 8,627
@Adam - the following Code will extract the contents of a BLOB Field into an ADODB Stream, write the Stream to a Unique File Name in a TEMP Directory under the Current Project Directory based on a Unique ID ([InvID]) and Extension ([sFileExtension]). These Files will then be dynamically loaded into an Image Control in a Report. Pay close attention to Code in the Click() Event of the single Command Button as well as the Format() Event of the Report's Detail Section. Make sure to set a Reference to the Microsoft ActiveX Data Objects X.X Object Library. I'll post the Code, but definitely download the Attachment to see what is going on.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdTest_Click()
  2. On Error GoTo Err_cmdTest_Click
  3. Dim strSQL As String
  4. Dim rstBLOB As ADODB.Recordset
  5. Dim mstream As ADODB.stream
  6. Dim strFullPath As String
  7.  
  8. 'Create a Directory named TEMP if it doesn't exist under the Current Project Path
  9. If Dir$(CurrentProject.Path & "\TEMP\", vbDirectory) = "" Then
  10.   MkDir CurrentProject.Path & "\TEMP\"
  11. End If
  12.  
  13. strSQL = "SELECT tblInventoryPics.* FROM tblInventoryPics"
  14.  
  15. Set rstBLOB = New ADODB.Recordset
  16. rstBLOB.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  17.  
  18. If rstBLOB.RecordCount = 0 Then Exit Sub
  19.  
  20. Set mstream = New ADODB.stream
  21.     mstream.Open
  22.  
  23. With rstBLOB
  24.   Do While Not .EOF
  25.     mstream.Type = adTypeBinary
  26.     mstream.Write rstBLOB.Fields("oPicture").Value          'Write to the Stream Object
  27.  
  28.     'Write BLOB to a File in the TEMP Directory under the Current Project Directory,
  29.     'with a Unique Inventory ID (InvID) and Extension (sFileExtension)
  30.     strFullPath = CurrentProject.Path & "\TEMP\" & ![InvID] & "." & ![sFileExtension]
  31.     mstream.SaveToFile strFullPath, adSaveCreateOverWrite
  32.       .MoveNext         'For each Record
  33.   Loop
  34. End With
  35.  
  36. rstBLOB.Close
  37. Set rstBLOB = Nothing
  38.  
  39. 'Open Report
  40. DoCmd.OpenReport "rptInventory", acViewPreview, , , acWindowNormal
  41. DoCmd.Maximize
  42.  
  43. Exit_cmdTest_Click:
  44.   Exit Sub
  45.  
  46. Err_cmdTest_Click:
  47.   MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
  48.     Resume Exit_cmdTest_Click
  49. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.   Me![imgPicture].Picture = CurrentProject.Path & "\TEMP\" & Me![InvID] & "." & Me![sFileExtension]
  3. End Sub
Attached Files
File Type: zip BLOBS.zip (870.6 KB, 732 views)
Mar 8 '11 #8

Rabbit
Expert Mod 10K+
P: 12,359
I'm not sure how it's more efficient to store image data in a blob. It's going to take up the same amount of space. And I would assume that retrieving the image would be much quicker on a file system than in a blob since you would have to write out the image data to a file before you can use it.

As far as data integrity goes, how is deleting the image file any different from deleting the image data in the blob? At least when it's in the file system, you might be able to retrieve it from the recycle bin. And if it's on a network location, you could retrieve the image file from a backup while to retrieve an Access database from a backup would roll back all changes made after the backup. Which would mean you would need to restore it to a different file name, find the record, and then import the blob back into the table.

Also, I would think that bloat would occur mainly on the Access end since there's not really a reason for bloat on a file system. In Access, when you delete a record, it doesn't free up the space in the database file unless you do a compact and repair. So you will end up with a database that constantly creeps up in size until you do a compact and repair. Also, since Access has a file size limit of 2 gigs, and image files can be rather large, you're going to hit this limit faster.
Mar 8 '11 #9

ADezii
Expert 5K+
P: 8,627
As far as using the File Path approach, I was always under the impression that:
  1. This technique cannot be used with Continuous Forms.
  2. A fully qualified UNC Path is a strict requirement.
  3. If you move the Images the Code will fail.
  4. If the Images reside on a Server and the Folder containing them is not shared, the Code will fail.
  5. If the Server is turned off, the Code will fail.
  6. If you not have Read Permissions to the PC, the Code will fail.
  7. etc...
  8. After all is said and done, storing Images as BLOBs becomes the obvious choice for Image storage. Storing Pictures as BLOBs in the Database is the most efficient way on containing Image Data because the Picture data is stored byte for byte, exact size, with the Images reflecting their true sizes.
  9. When BLOBs are stored in the Database with other data, BLOB and tabular data are backed up and recovered together and are synchronized, there are no File Paths to contend with, and no resultant Database bloating. Data consistency is ensured because INSERT, UPDATE, and DELETE operations occur in the same transaction. Separate security measures need not be created since BLOB and regular data coexist.
Mar 8 '11 #10

Rabbit
Expert Mod 10K+
P: 12,359
  1. What do you mean by it can't be used with continuous forms? Do you mean if you wanted to display the picture? Doesn't the same problem exist for trying to display the blob data in a continuous form? You would have to export the blob data into an image file and then set the property of the image control. I don't think that would work on a continuous form either.
  2. I don't see a problem with storing a relative path with the images in a folder where the database is stored.
  3. Which is why I would use a relative path. If you move the images, you would move the database with them I would assume.
  4. If the images need to be shared, then so does the database. Keep them in the same parent folder and the permissions will be inherrited.
  5. I would assume everything would be kept on the same server.
  6. See number 4.
  7. etc.
  8. Actually, it's not byte for byte. At least not according to this article by Microsoft. http://office.microsoft.com/en-us/ac...005280225.aspx. Apparently, Access will create additional bitmaps that go along with each image you store in the database. So you're storing more than just the file.
  9. I think the second half of that statement could be reasons to use a blob over storing a path. Whether or not it's enough to justify it, I don't know. I do, however, contend that there will be more bloating in Access. Not just due to the article linked above, but because Access doesn't free up unused space in the database file until you run a compact.
Mar 9 '11 #11

100+
P: 137
my point was that if the user deleted the original file, unaware that it was still being used by the database, then it could cause problems.
Yes I agree that bloat isn't properly handled until the database is compacted, but surely it makes sense to try and prevent it in the first place (or at least restrict it's level of growth) instead of fixing it later?

I was under the impression that the 'creating additional bitmaps' problem comes with just straight up embedding images in an OLE Object field. This is why I mentioned bloating, as the bloating is caused by the additional creation of bitmap images.
BLOBS are stored in Binary Large Data fields, and don't create bitmap images - probably the reason why you can't just straight link BLOB images to bound object frames.

I could be wrong, but that's what I've read.


Thanks very much for the code ADezii, i'll see how I get on with it!
Mar 9 '11 #12

Rabbit
Expert Mod 10K+
P: 12,359
Accidentally deleting of files could be a problem but so could accidentally deleting the data in the blob. I just think it's much easier to recover a deleted file than blob. Also, the user shouldn't be in that folder changing stuff anyways.

You really can't prevent bloat in Access without a compact. You said that there is bloat with the other methods like the file system method. But I don't see how there's any bloat by storing images on the file system. I imagine there's more bloat by storing it in Access.

The OLE Object field is a BLOB field. You can store byte for byte only if you do all inserts and updates through VBA coding. Otherwise, it will create the bitmaps.

How big is your data set? Access has a limit of 2000 megabytes. Assuming an average picture size of 1 megabyte and assuming you're using VBA to insert and update the BLOB data, you're looking at a maximum of 2000 records. Not counting the size existing forms, reports, other tables, etc. So if you may hit 2000 records at some point, it's not going to work.

But even assuming you only have 100 records, every time someone views the report, it has to read 100 records worth of images, write 100 files, and read those 100 files into the report.
Mar 9 '11 #13

100+
P: 137
Right I've managed to successfully implement your code onto my db, and it does what it should. I do however have a couple of queries:
Should the TEMP folder not be deleted once the database is closed? In a multi user environment would it cause problems if they were deleted while a user was still using them?
Should there be any bloating or performance issues related to doing this? Just by creating the report and opening it, my database has shot up from 6188kb to 13768kb, and my whole machine was suffering from a bit of lag after doing so. Performance quickly went back to normal, but the size issue still remains, even after a compact.
Mar 9 '11 #14

Rabbit
Expert Mod 10K+
P: 12,359
From the look of ADezii's code, once the user is finished with the pictures, it doesn't matter what happens to the temp folder and its contents because the folder will be recreated if it's not there. The pictures will be recreated each time.

If a user was looking at the report and the files get deleted, then those pictures that had not yet been rendered by the report would be missing from the report.

The lag you experienced is due to when you view the report, it has to read the blobs, write the files, and load those files into the report. The bottleneck is most likely the writing of the file. You can mitigate this using a RAID array of hard drives.

If you will be using this in a multi-user environment, I suspect there may be write conflicts if two users tries to view the report at the same time and it tries to write to the same file at the same time. One of the users will get an error.
Mar 9 '11 #15

ADezii
Expert 5K+
P: 8,627
You are quite welcome, good luck Adam, and let us know if you need anything else.
Mar 9 '11 #16

ADezii
Expert 5K+
P: 8,627
@Adam - Here is a Demo DB on BLOBs that you may find very interesting, and well as very informative in your case. The Base Code is by Alan Warren, but I made several modifications to it, and use this DB as a Template anytime I am involved in a Project that comprises BLOBs. Enjoy and have fun...
Attached Files
File Type: zip BLOBS.zip (861.7 KB, 678 views)
Mar 9 '11 #17

100+
P: 137
If it's a split database would that not eliminate the conflict problem?



Thanks both for the information, and cheers for the file ADezii i'll keep that in my collection. :)
Mar 10 '11 #18

Rabbit
Expert Mod 10K+
P: 12,359
If the database is split, and each user has the front end on their computer, then there would be no conflict. However, you indicated that there's a possibility someone else might delete the images while someone was viewing the report. This suggests that the front end will be stored on the network and shared by the users. In this case, there is the possibility of conflicts. An alternative then, is to change the code so that the image folder gets created in the user's profile folder.
Mar 10 '11 #19

100+
P: 137
Thanks, but I am not looking to buy or download anything, as that is not a viable solution for the situation.
Mar 14 '11 #20

ADezii
Expert 5K+
P: 8,627
@Adam - There also exists a 'DAO based' approach to writing Binary, BLOB Data to a Disk File. I'm not sure how it compares to the ADODB approach, but it may/may not be faster and more efficient. If you like, I'll Demo it for you, just let me know.
Mar 14 '11 #21

100+
P: 137
Sorry to bump an old thread, but I had one final question around this topic:

Regarding the temp folder that gets created for the blob files - if the database was split, with a front end on each user's machine instead of a network, would the temp folder be created in the location of the front end, or the location of the back end?

Only reason I ask is it would seem sensible if the folder was deleted once the database is closed - if it's created in the front end location then doing this shouldn't cause conflict if one user was accessing it while the other person deleted it, as they'll each have their own temp folders to use for generating pictures in the reports.
Mar 23 '11 #22

ADezii
Expert 5K+
P: 8,627
if the database was split, with a front end on each user's machine instead of a network, would the temp folder be created in the location of the front end, or the location of the back end?
To the best of my knowledge, the TEMP Directory would be created on the Front End, unless specifically directed otherwise, via an Absolute PATH.
Mar 23 '11 #23

100+
P: 137
Ok thanks Adezii. I'll have to play around with it when I fix everything else! :)
Mar 23 '11 #24

Post your reply

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