none
Rendering stored image on report RRS feed

  • Question

  • I have a database of employees, I want to store employee pictures in their employee record.  Currently I am storing the image in an OLEObject.

    How do I get the stored image to render when printing a report or display on a form?

    I assumed I could place the OLEObject on the report and form and it would display.

    Darcy

    Saturday, December 3, 2016 11:01 PM

Answers

  • A more economical way of showing images in a database is to store the path to an image file and load the image into an Image control at runtime.  You'll find examples of varying degrees of complexity in Images.zip in my public databases folder at:

     

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

     

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly. 

     

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

     

    In your case the basic Images_Simple.accdb file would be an appropriate model if you wish to show only one image per employee record.  This illustrates how to show the image both in a form and in a report.

     

    One thing to note is that, as modern digital cameras create very large image files, these can be a little slow in loading at runtime, so it's best to use lower resolution versions of such files.  Any imaging software should be able to create a lower resolution version of a file where necessary.


    Ken Sheridan, Stafford, England

    Sunday, December 4, 2016 1:01 PM

All replies

  • A more economical way of showing images in a database is to store the path to an image file and load the image into an Image control at runtime.  You'll find examples of varying degrees of complexity in Images.zip in my public databases folder at:

     

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

     

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly. 

     

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

     

    In your case the basic Images_Simple.accdb file would be an appropriate model if you wish to show only one image per employee record.  This illustrates how to show the image both in a form and in a report.

     

    One thing to note is that, as modern digital cameras create very large image files, these can be a little slow in loading at runtime, so it's best to use lower resolution versions of such files.  Any imaging software should be able to create a lower resolution version of a file where necessary.


    Ken Sheridan, Stafford, England

    Sunday, December 4, 2016 1:01 PM
  • Thank You!!
    Sunday, December 4, 2016 9:46 PM
  • Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long


    Private Function SelectPicture() As String
      Dim strSQL As String
      Dim cnnADO As ADODB.Connection
      Dim rstADO As New ADODB.Recordset
      Dim streamADO As New ADODB.stream

      On Error Resume Next
      Set cnnADO = CurrentProject.Connection
      strSQL = "SELECT e_photo FROM employees WHERE e_id = " & e_id
      With rstADO
        .Open strSQL, cnnADO, adOpenKeyset, adLockOptimistic
        With streamADO
          .type = adTypeBinary
          .Open
          If Not IsNull(rstADO.fields("e_photo").value) Then
            .Write rstADO.fields("e_photo").value
            .SaveToFile GetTmpPath & "!EPICT.JPG", adSaveCreateOverWrite
            SelectPicture = GetTmpPath & "!EPICT.JPG"
          Else
            SelectPicture = ""
          End If
        End With
       .Close
      End With
      cnnADO.Close
      Set rstADO = Nothing
      Set streamADO = Nothing
      Set cnnADO = Nothing
    End Function

    Private Sub InsertPicture_Click()
      Dim strSQL As String
      Dim cnnADO As ADODB.Connection
      Dim rstADO As New ADODB.Recordset
      Dim streamADO As New ADODB.stream
      Dim pd As Variant

      On Error Resume Next
      Set cnnADO = CurrentProject.Connection
      strSQL = "SELECT e_photo FROM employees WHERE e_id = " & e_id
      With rstADO
        .Open strSQL, cnnADO, adOpenKeyset, adLockOptimistic
        streamADO.type = adTypeBinary
        streamADO.Open
        streamADO.LoadFromFile "c:\" & e_id.Column(1) & ".jpg"
        .fields("e_photo").value = streamADO.Read
        .Update
        streamADO.Write .fields("e_photo").value
        streamADO.SaveToFile GetTmpPath & "!EPICT.JPG", adSaveCreateOverWrite
        e_photo.Picture = GetTmpPath & "!EPICT.JPG"
       .Close
      End With
      cnnADO.Close
      Set rstADO = Nothing
      Set streamADO = Nothing
      Set cnnADO = Nothing
    End Sub

    Public Function GetTmpPath()
      Dim sFolder As String ' Name of the folder
      Dim lRet As Long ' Return Value
      Const MAX_PATH = 260
      sFolder = String(MAX_PATH, 0)
      lRet = GetTempPath(MAX_PATH, sFolder)
      If lRet <> 0 Then
        GetTmpPath = LEFT(sFolder, InStr(sFolder, Chr(0)) - 1)
      Else
        GetTmpPath = vbNullString
      End If
    End Function

    e_photo.Picture = SelectPicture 'This shows the picture

    The above is used against tables located on SQL Server. backend You can skip the TempPath and use something else for simplicity.

    e_photo field is of SQL Server Image type.

    Greg

     


    • Edited by SysGreg Thursday, December 8, 2016 12:16 AM
    Thursday, December 8, 2016 12:01 AM