[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Display JPEG blob in Form/Report

Posted on 2002-06-25
14
Medium Priority
?
510 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

656 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