Solved

Display JPEG blob in Form/Report

Posted on 2002-06-25
14
507 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

691 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