3

In an Access 2013 Report I connect to an Oracle DB View via a Linked Table. One View field is of type BLOB and holds images that have been previously stored there.

In my Access Report the linked table has a field (named BILD_INHALT) of type "OLE Object" that maps to the BLOB field in Oracle DB.

In the report view, I created an "Image" field (named MST_Image) has to load the image and it is initialized in the following way:

If Len(RS![BILD_INHALT]) > 0 Then
   Me.MST_Image.PictureData = RS![BILD_INHALT]
End If

At run time I get the following exception when I initialize MST_Image.PictureData with the content coming from Oracle.

enter image description here

I check on internet about further documentation about this (very old) topic, but I could not find anything about this specific issue.

Community
  • 1
  • 1
Francesco
  • 9,947
  • 7
  • 67
  • 110

1 Answers1

2

The error is somewhat self-explanatory: an image control in Microsoft Access can be either bound to a file location, or can be set to a .dib image (device independent bitmap format, one of the more obscure image formats).

Working around it, though, is not that easy.

You can work around that limitation in several ways:

  1. Use an ActiveX control that supports multiple image formats (there are multiple to be found)
  2. Save the image to disk in a temporary folder, and set the images control source to its location
  3. Use a web browser control, and use the HTML <img /> tag to display your image, using an embedded BASE64 image

Here is the example code for approach 3:

First, we need to be able to convert the binary code contained in the OLE object to BASE64:

Public Function ToBase64(Bytes As Variant) As String
    Dim XMLElement As Object
    Set XMLElement = CreateObject("Msxml2.DOMDocument.6.0").createElement("tmp")
    XMLElement.DataType = "bin.base64"
    XMLElement.nodeTypedValue = Bytes
    ToBase64 = Replace(XMLElement.Text, vbLf, "")
End Function

Then, we can use a web browser control, and insert a web page with the BASE64-encoded image into it:

Public Sub InsertImageInControl()
    Dim wb As Object
    Set wb = MyWebbrowserControl.Object
    With wb
        .Navigate2 "about:blank"
        Do Until .ReadyState = 4 '=READYSTATE_COMPLETE
            'This is a somewhat inefficient way to wait, but loading a blank page should only take a couple of milliseconds
            DoEvents
        Loop
        .Document.Open
        .Document.Write "<!DOCTYPE html><HTML><HEAD><TITLE>A</TITLE></HEAD><BODY scroll=""no"" style=""margin: 0px; padding: 0px;"">"
        .Document.Write "<img alt="""" style=""width:100%; margin: 0px; padding: 0px;"" src=""data:image/jpg;base64,"
        .Document.Write ToBase64(MyOLEObject.Value)
        .Document.Write """ />"
        .Document.Write "</BODY></HTML>"
        .Document.Close
    End With
End Sub

Where MyWebbrowserControl is the name of your webbrowser control, image/jpg is your image type, and MyOLEObject is your OLE object.

Tips:

  • Don't use the WebBrowser ActiveX control, but use the one that comes with Access. Else, you will get an outdated version of Internet Explorer with a 3d border that can't be removed.
  • Set the control source for the web browser control to ="about:blank" to initialize it as a blank page
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Many thanks Erik for the answer. The Access report has been written in VB.net by a colleague of mine and the Oracle DB source (where the images are stored as BLOBS) is under the client's control. As I have only limited time to solve the issues, I will try with your 2nd suggestion. – Francesco Oct 18 '17 at 06:53
  • @Francesco As said, I do have example VBA code for the third solution, and while it may not be short, it should require little to no modification. The 2nd solution, however, is fine too. I recommend reading [Chip Pearsons working with temporary folders and files in VBA](http://www.cpearson.com/excel/WorkingWithTempFilesAndFolders.htm) if you go for that. – Erik A Oct 18 '17 at 06:59
  • I would be grateful if it would be possible to see your VBA example code, do you have it published on a blog or can you tell me how to get it? Thanks again – Francesco Oct 18 '17 at 07:01
  • I've added the example here, you can review it and ask questions if you wish. Note that Internet Explorer tends to ignore the `data:image/jpg` image type, so if the image type is not constant, it tends to work fine too. – Erik A Oct 18 '17 at 07:10
  • Can I use "MyWebbrowserControl.Object" directly instead of my Image Control? The image would be just a portion of the whole Report (more or less a Logo), therefore should fit inside the report instead of being an independent page. – Francesco Oct 18 '17 at 07:42
  • Yes, just replace your image control with a web browser control. The web browser control will only display the image. Note that it does allow right clicking it, viewing the web page source code, and saving the image. You can add some javascript to prevent this. – Erik A Oct 18 '17 at 07:43
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/156983/discussion-between-francesco-and-erik-von-asmuth). – Francesco Oct 18 '17 at 08:40