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.
23 22289 ADezii 8,834
Recognized Expert Expert @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: - Extract the Binary Data comprising the BLOB from the OLE Object Field and write it to an ADO Stream Object.
- Save the contents of the Stream Object (BLOB) to a Temp File with a Primary Key as a Unique Identifier.
- 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.
- Before you even attempt this approach, you must know the File Extension associated with each BLOB (.bmp, .jpg, etc.).
- If you do know the Extension for each BLOB, then I will be happy to test this Theory for you, just let me know.
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.
ADezii 8,834
Recognized Expert Expert
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.
Rabbit 12,516
Recognized Expert Moderator MVP
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.
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.
ADezii 8,834
Recognized Expert Expert @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.
ADezii 8,834
Recognized Expert Expert @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. - Private Sub cmdTest_Click()
-
On Error GoTo Err_cmdTest_Click
-
Dim strSQL As String
-
Dim rstBLOB As ADODB.Recordset
-
Dim mstream As ADODB.stream
-
Dim strFullPath As String
-
-
'Create a Directory named TEMP if it doesn't exist under the Current Project Path
-
If Dir$(CurrentProject.Path & "\TEMP\", vbDirectory) = "" Then
-
MkDir CurrentProject.Path & "\TEMP\"
-
End If
-
-
strSQL = "SELECT tblInventoryPics.* FROM tblInventoryPics"
-
-
Set rstBLOB = New ADODB.Recordset
-
rstBLOB.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
-
-
If rstBLOB.RecordCount = 0 Then Exit Sub
-
-
Set mstream = New ADODB.stream
-
mstream.Open
-
-
With rstBLOB
-
Do While Not .EOF
-
mstream.Type = adTypeBinary
-
mstream.Write rstBLOB.Fields("oPicture").Value 'Write to the Stream Object
-
-
'Write BLOB to a File in the TEMP Directory under the Current Project Directory,
-
'with a Unique Inventory ID (InvID) and Extension (sFileExtension)
-
strFullPath = CurrentProject.Path & "\TEMP\" & ![InvID] & "." & ![sFileExtension]
-
mstream.SaveToFile strFullPath, adSaveCreateOverWrite
-
.MoveNext 'For each Record
-
Loop
-
End With
-
-
rstBLOB.Close
-
Set rstBLOB = Nothing
-
-
'Open Report
-
DoCmd.OpenReport "rptInventory", acViewPreview, , , acWindowNormal
-
DoCmd.Maximize
-
-
Exit_cmdTest_Click:
-
Exit Sub
-
-
Err_cmdTest_Click:
-
MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
-
Resume Exit_cmdTest_Click
-
End Sub
- Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
-
Me![imgPicture].Picture = CurrentProject.Path & "\TEMP\" & Me![InvID] & "." & Me![sFileExtension]
-
End Sub
Rabbit 12,516
Recognized Expert Moderator MVP
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.
ADezii 8,834
Recognized Expert Expert
As far as using the File Path approach, I was always under the impression that: - This technique cannot be used with Continuous Forms.
- A fully qualified UNC Path is a strict requirement.
- If you move the Images the Code will fail.
- If the Images reside on a Server and the Folder containing them is not shared, the Code will fail.
- If the Server is turned off, the Code will fail.
- If you not have Read Permissions to the PC, the Code will fail.
- etc...
- 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.
- 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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Robert |
last post by:
Hi
I searching in other groups replay but .... :-(
Wy my code
(print ("<P> <IMG
|
by: mar10a |
last post by:
I am trying to insert a .gif. file into an Access report - using
Insert Picture.
The original image is 8X11, but access changes the size to 4X6.5.
When I change the size of the image in access is puts white border
around it and does not change the actual image.
Is there a setting I need to change that would allow access to import
at the correct size, or do I need to use another file type besides
|
by: M P |
last post by:
Hi!
I am currently working on a project that will migrate MS Access DB and Front
End to web-base solution to reduce MS Access installation. Previously, users
are using MS Access as frontend to produce customized report. I am not sure
if in a web based solution, using ASP, I can display the access report as-is
or by converting to pdf (server side) and display on a webpage. I'm not sure
if there are builtin solution on this or I need to...
|
by: SLeininger |
last post by:
I get a red X when I try to import my BLOB images out of SQL server. I only have two fields 'PersonIdNo' is the key and 'Photo' is the BLOB images (jpg).
Here is my page so far:
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As...
|
by: keithsimpson3973 |
last post by:
Does anyone know if it is possible to display the value of a date picker control on a vb6 form that the user selected on an access report? I am using vb6 to display a report selection and it has a date picker combo for the start date and another for the stop date. I would like to display those 2 dates on the header of the access report I am opening from Visual Basic 6. Thanks for any help anyone can give....
| |
by: smorrison64 |
last post by:
I have a form that is coded to open a File Dialog boc to pick a picture to display on that particular item on a subform. My form is not based on query, but rather two separate tables (one primary, one sub). That code is working properly. How do I get that to translate to my report? I use VBA code because I use formats other than BMP for the pictures. Would basing the form on the qury that the report is based on solve the issue without...
|
by: satishbeh |
last post by:
I have programatically saved a picture to a table as Blob format i.e long binary format. When I linked the field to a access report, it does not print. I tried using a third partysoftware like DBPix and embedded it into the report, but it says the picture is too small.
Is there any way to save a picture progmatically and display it on the report
Regards,
Satish Menon
|
by: selvasoft |
last post by:
Hi
Please help me any one. I want solution for display multiple images from oracle database.Using JSP.
here is my code for display one image from database.
Please Any one give me some ideas.
<%@ page import ="java.sql.*,java.util.*,java.awt.*,java.io.*" %>
<%
|
by: dbdb |
last post by:
hi,
i work with ms. access 2003
i want to display image from a folder to the report.
i have read thread in http://bytes.com/topic/access/answers/191611-pictures-access-reports
but that's for access 2000
i want to set my image picture property, but there is no picture property when i type "image.p " -> no picture property.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |