Show a picture stored in Access on an Access form

Sorry Jeff, but that's exactly what I DON'T want to do. I am developing a standard product that will be sold to a small number of clients. On the main form, I want to show the client's logo to peronalize the app a bit. I want to have that client logo in the DB so that I'm independant of the installation setup. So I don't care about the bloat. The logos are small anyway, I'm not talking about fancy 10 megapixels photos, but about small bitmaps (3-5 KBytes) :-)

So I need a way to show my already stored logo somewhere on a form... Do you have a solution for that ?

Regards
Bernard

bthouin,

You can certainly create an OLE field in a table and insert/embed images into it.
If you then create a form or report from this table, the image will display.
(I know this is not what you are after, I am just mentioning it.)

But I am not sure how you would reference this stored image in a control.

Anytime you insert an image control it wants a path to an external image. I, personally, have never been able to make an Image control reference an image stored in a Table.

If you just use a standard image control you can point it to any external image.
(In order to embed/store the image in the control, DO NOT check on the option button for "Link".
Since you probably wont be adding Reports/Forms all that often, (and the images will probably be small) you can just copy and paste the image control Logo from one Report/Form to another.

JeffCoachman

Hi Jeff

>>I, personally, have never been able to make an Image control reference an image stored in a Table<<

It MUST be possible, though, 'cause I found an example .mdb on the Internet which has 2 pictures stored in a table in the DB, and loads them alternatively into a command button when you click on the command button. I enclose the app for your perusal

I am familiar with the sample you posted,

In that sample the images are "embedded" in the form.
Using the code, the images are converted to "BLOBS" (B)inary (L)arge (O)(B)jects.
Then stored in a table, then retrieved, and displayed into the sample form.

You stated that your images are stored as OLE Datatypes, and that you:
 "manged, by using code found on the Internet, to store a bitmap image in my table"
But you did not explictly say how you did this or what format/Datatype you converted them to.
The you went on to say:
 " Can someone tell me how to "read in" my OLE object out of my simple table "
?
So are the images now OLE objects again?

Can you clearly state what Datatype you images are currently in?


The Code in this sample (AFAICT) converts embedded images to BLOBS, not OLE images to BLOBS.
If your images are truly BLOBS, then this code should work for you.

OLE Fields were meant only to give you Access to the file.
Typically the OLE file will open in it's native application.

See here for info on converting OLE Fields to BLOBS:
http://www.access-programmers.co.uk/forums/showthread.php?t=119979

http://www.experts-exchange.com/Programming/Editors_IDEs/PowerBuilder/Q_20951167.html

(Can't be done?)
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20431268.html

Let me know how you make out.

JeffCoachman

Hi Jeff

Working on it, to make a nice demo of where the problem lies: I can effectively show the images stored in that app I sent you, I can't show my own, so the solution must have to do something with the way I stored my bitmap image in the Access DB. To do that, I defiend a table with a picture name field (text) and an image field (OLE object, the famous one). So did the guy who wrote the app I sent you. I store dthe bitmap in it like this:

That image can NOT be retrieved by:

Set rs = CurrentDb.OpenRecordset("SELECT Image FROM tbLogos WHERE ImageName = '" & logo & "'", dbOpenDynaset)
 Me.imgBox.PictureData = rs!Image

The 2nd statement gives me the famous error about the wrong type of bitmap.

However, the very same 2 statements work perfectly well on the table in the app I sent you, although that table at first sight looks exactly the same.

So the difference must be in the way the images were stored in that app. Unfortunatelym, that's exactly the thing which is missing in that app, it doesn't say how the images were stored.

My storage method is found in several internet articles about storing images in Access, but there still must be something "wrong" with it. But what ???

Let's hope you can tell me what...:-)

Cheers
Bernard

Public Function StoreImage(fileName As String)
    Dim fileNameShort As String
    Dim FileBinary As String
    Const blockSize = 32768
    Dim NumBlocks As Integer
    Dim i As Integer
    Dim LeftOver As Long
    Dim FileLength As Long
    Dim rs As Recordset
    Dim sArr As Variant
    
    sArr = Split(fileName, "\")
    fileNameShort = sArr(UBound(sArr))
    Set rs = CurrentDb.OpenRecordset("tbLogos", dbOpenDynaset)
    rs.AddNew
        rs!ImageName = fileNameShort
    rs.Update
    Set rs = CurrentDb.OpenRecordset("SELECT Image FROM tbLogos WHERE ImageName = '" & fileNameShort & "'", dbOpenDynaset)
    rs.Edit
        Open fileName For Binary Access Read As #1
        FileLength = LOF(1)
        NumBlocks = FileLength \ blockSize
        LeftOver = FileLength Mod blockSize
        FileBinary = String$(LeftOver, 32)
        For i = 1 To NumBlocks
            Get #1, , FileBinary
            rs("Image").AppendChunk FileBinary
        Next i
    rs.Update
    Close #1
    rs.Close
    Set rs = Nothing
    
End Function
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:

Again, from what I remember about that file, the images are Embedded in the Access program.
Notice how they represent Access "Icons".
The are not actually stored in the table.

The code is what inserts them into the table.

Notice the the table only has two images in it, while the form has about six.

I have tried to contact the developer who created that file, he is a member here.

Jeff

JeffCoachman

There are ultimately (at least) three formats of image storage at play here.

The much maligned "OLE field" is a required data type.
OLE Objects however are an Access concept where binary objects are inserted via Access functionality with wrapping required to allow rendering (convenient display) within the Access UI.  This convenience is what comes at the cost of bloating in DB size.
One main advantage of the OLE Object (prior to Access 2007) is that it can be used in continuous forms to display distinct, bound objects (images).

BLOBs are different in that they are inserted "by hand" in that we do it ourselves in code - stuffing the raw binary data into the field.  But it's still in the "OLE Field" data type.  (It is nothing more than a Long Binary data type - the type of which is supported in any any reasonable RDBMS).  That we often see "OLE Objects" in there gives them a bad rep.
BLOB data (images) in OLE Fields need to be extracted before they can be used as such again.  This takes a definite overhead and lag time.  (See the BLOB demo in the examples page link in my profile).

The example in this thread is a third image type.
Access image controls display images from disk (and can be linked or embedded data).
The binary data of that image again contains a small amount of Access formatting data (nothing like the OLE Objects - a tiny amount).  That image data in the control is exposed (and assignable) through the Image control's PictureData property.
We can store that data aquired from that property (stashed in an OLE - Long Binary - field or even a Memo field is capable of filling the role).  And then re-assign it to other controls at will.
Why?
Efficient storage (the data's the same size as the file from which it came).  Re-usable images (the single stored image data can be used in multiple forms and reports without the bloat of embedding in each UI object or the time lag in loading from disk - as loading the PictureData property is much quicker than a disk read of a file).

However the Access formatted images (OLE Objects and PictureData stored data) can't be readily used as BLOB data to create usable files.  If you have stored OLE Objecs then Stephen Lebans has provided a mass extract to disk utility.

The example from this thread stores PictureData property data and re-sets it.
Data stored by other means needs to be assigned by other means.
Your StoreImage example is inserting BLOB data.  To display that in a native Access image control then you need to extract the image to file again and set an Image control's Picture property to that path.

The final thing to mention is Access 2007's Attachment data type (part of the new Multi-Value field functionality) allows both efficient insertion of file data (it's compressed upon insertion) and the bound display of images (as OLE Objects can) including continuous view.
2007's Image controls also support binding to a Text data field which describes an image path.  (Which again allows different images in continuous view).  Useful and welcome functionality.

Thanks for helping out here Leigh.
;-)

Jeff

bthouin,

See here for other worthwhile information:
http://www.lebans.com/oletodisk.htm

Jeff

Yep - that's the util.  (Applicable to OLE Objects - which are notoriously hard to extract otherwise).

So, if I understood you correctly, Leigh, I should:
- first load my bitmaps one after the other into an image control in Access. This will give them this famous "small amount of Access formatting data" which later on will make the image control accept it again as proper PictureData for the image control
- then store each of the image control content into the database using the code that I showed in my last mail, but of course not by reading a file but the PictureData instead chunk by chunk into the infamous OLE object column of my image table
- then load that data back into the image control's PictureData, using of course the image I want to appear there

Is that correct ?

@Jeff: just wanted to tell you that no, "the images are" NOT "embedded in the Access program" (the one app I sent you), they are in a table in that Access DB. To show the images the code is making a DLookUp. That's definitively getting the images from the DB :-)

@both of you: thanks a lot for your profound knowledge. I'm stunned, however, to discover that what I thought was a VERY simple problem turns out to be such an incredibly complex feast.

Thanks again. I'll keep you posted about my progress when I get time to try out. And just tell me if the above is wrong ! And you'll get the points when I've succeeded, that might take a few days...

Cheers
Bernard

bthouin,

"the images are" NOT "embedded in the Access program" (the one app I sent you), "

Yes, this I knew, I was referring to the sample you were using as a reference
(where there were two image entries in the table, but Image form had about 5 images on it.
The button images on the form are Embedded, then the code converted them, then stored them in the table.

But I think LPurvis explained this a lot better than me.
;-)

Sorry for the confusion.
;-)

Keep us posted on your progress

Jeff