Solved

Display JPEG blob in Form/Report

Posted on 2002-06-25
14
503 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now