Solved

Display JPEG blob in Form/Report

Posted on 2002-06-25
14
505 Views
Last Modified: 2012-08-13
OK guys, I am a SQL DBA but I need to produce a report with graphics.

We have a SQL server with a blob field containing a jpeg image.

I need to produce a nice report with standard record details on each page, along with the jpeg image.

I have Access 97 and Access 2000 at my disposal.

I have read various questions regarding linking to JPEG files in the filesystem, but no can do in this case, I must link to the BLOB.

Any ideas please?
Thanks in advance.
0
Comment
Question by:lozzamoore
  • 4
  • 3
  • 3
  • +3
14 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7108225
I believe that if you link the SQL Server to Access, Access will see the BLOB field as an OLE Object, and you can then simply drag-and-drop that OLE Object field onto the report.  I just did this with the Employees table in the Northwinds.mdb sample application which comes the Access (the Employees table has a Photo field(type OLE Object) which hold the employee's Photo (amazing how the name of the field helps in that way LOL), and I just created a report, bound to that table, and dragged the Photo Filed onto the Report design window and voila, it works like a champ.

Arthur Wood
0
 
LVL 7

Author Comment

by:lozzamoore
ID: 7108305
Thanks Author_Wood.

I have played a little, and you are right, Access sees the BLOB field as an OLE Object.

However, just doing the standard stuff you mention and dragging the field onto the form/report gives me nothing but white space when I run the form/report.

Don't know what I am missing? Could it the linked table to SQL be an issue? Perhaps you could check this on your installation there, if you have access to SQL.

Thanks,
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7108367
sorry, but I don't have a SQL Server installation available.  Haven't got any useful ideas, either.

Arthur
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7108384
just a thought, but the problem MAY arise when you LINK to the table.  What happens if you CREATE a LOCAL COPY of the SQL Server table(IMPORT the SQL Server Table, rather than LINK), and THEN use the OLE Object field?  I have had problems in the past with Linked Tables, along those lines (though not THAT specific problem).  This is just a thought, and not a very pallatable solution, as it would require re-creating the local copy periodically, if the pictures in the SQL Server DB change often.  If they don't change, then this MAY 9again just a guess) help???
0
 
LVL 28

Expert Comment

by:TextReport
ID: 7108495
From memory the main problem you will find is that the BLOB in SQL doesn't contain the OLE Wrapper needed to associate the image to an application and therefore can't be displayed.

Try inserting an image through the access form into the BLOB and see if that then displays. if it doe you may have to re insert all your images from file to sql server unless you can find an apropriate OLE Application.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7108967
0
 
LVL 7

Author Comment

by:lozzamoore
ID: 7110191
Ran an import of all the data, (as this will be a one of report (hopefully!))

Still get blank bound object frame.

Anything else that I could be missing?

Thanks for link, but I don't really want to have to do reading and writing of BLOBS to/from file system if I can help it.

Cheers,
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7110560
The link was intended to provide code to access the BLOB in the (linked) table, as it seemed the problem to manipulate the BLOB without being embedded with OLE. ;-)

As I don't have MS SQL at the moment, I can't test my statement ;-(

Nic;o)
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 7110590
what is the datatype you're using on the sql server? if it's varbinary, change it to image then re-link your table... now when you drag the field, it should work just dandy...

dovholuk
0
 
LVL 7

Author Comment

by:lozzamoore
ID: 7113041
Dovholuk,

Datatype on SQL server is image.
Datatype now within imported access table is OLE Object

Thanks,
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 7113173
so is the situation fixed or was this the current situation?

did you re-link the table in access? can you now see the pics?

as a test, try adding a bmp file to a row in your table by dragging and dropping it (as mentioned above) into your table, onto the OLE field. will it "take" the import? if yes, does it display on the report?

maybe there's an issue with .jpg's... i only tested image fields with .bmp's... that may make a difference...

-dovholuk
0
 
LVL 7

Author Comment

by:lozzamoore
ID: 7114218
Dovholuk,

Current status is this.
Have now imported all data into Access from SQL.
Tried dragging and dropping BMP file, and this displays fine. Dragged and dropped an JPG and this is displayed as an icon that can be clicked and opens in browser. (even though image control 'display type' value is set to 'Content'.

The image is displayed if I set the control to image and not bound object frame, but I can only get this working with manual file paths.

I am pretty much resided to the fact that I need to do this by exported all images into a folder (which I can do reasonably easily and quickly from SQL).
Then use a little bit of access code to set the path for the image every time a new record is selected.

Can anyone see any other plan that would work?
If not, can they give me instructions on how to achieve this?
Will this approach work with a report, so that I can print off the report and a different photo will appear on each page?

Thanks,
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7254153

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts refunded
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7270081
Per recommendation, points refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Use a Combo-Box to do a Search 26 25
SQL - Cumulating field values to one field 5 19
Modal form 11 29
aggregate query? 3 29
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question