<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Show pictures directly from URLs in Access forms and reports

Published on
5,867 Points
367 Views
5 Endorsements
Last Modified:
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder.

But why not let Windows retrieve the pictures automatically from the URLs?

Scenario 1 - Download and Display

To display a picture from some remote location in your form or report, you would first download the picture file, save it in a folder, and then specify in your application where the picture is located. The file can be saved locally or at a shared folder on the network.


When you open your form or report, the picture control uses that information to retrieve the picture and display it.


This means that all pictures files have to have been downloaded and stored before the form or report is opened or the pictures cannot be displayed and may cause rendering of the report or form to appear garbled.


The cleanest, simple, and speedy method is to use an API call URLDownloadToFile . The below function shows how to download the images to a specified path:

Option Compare Database
Option Explicit

' API declarations.
'
Private Declare Function URLDownloadToFile Lib "Urlmon" Alias "URLDownloadToFileA" ( _
    ByVal pCaller As Long, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserved As Long, _
    ByVal lpfnCB As Long) _
    As Long

' Download a file or a page with public access from the web. 
' Returns 0 if success, error code if not. 
' 
' If parameter NoOverwrite is True, no download will be attempted 
' if an existing local file exists, thus this will not be overwritten. 
' 
' Examples: 
' 
' Download a file: 
'   Url = "https://www.codeproject.com/script/Membership/ProfileImages/%7Ba82bcf77-ba9f-4ec3-bbb3-1d9ce15cae23%7D.jpg" 
'   FileName = "C:\Test\CodeProjectProfile.jpg" 
'   Result = DownloadFile(Url, FileName) 
' 
' Download a page: 
'   Url = "https://www.codeproject.com/Tips/1022704/Rounding-Values-Up-Down-By-Or-To-Significant-Figur?display=Print" 
'   FileName = "C:\Test\CodeProject1022704.html" 
'   Result = DownloadFile(Url, FileName) 
' 
' Error codes: 
' -2146697210   "file not found". 
' -2146697211   "domain not found". 
' -1            "local file could not be created." 
' 
' 2004-12-17. Gustav Brock, Cactus Data ApS, CPH. 
' 2017-05-25. Gustav Brock, Cactus Data ApS, CPH. Added check for local file. 
' 2017-06-05. Gustav Brock, Cactus Data ApS, CPH. Added option to no overwrite the local file. 
' 
Public Function DownloadFile( _ 
    ByVal Url As String, _ 
    ByVal LocalFileName As String, _ 
    Optional ByVal NoOverwrite As Boolean) _ 
    As Long 
 
    Const BindFDefault  As Long = 0 
    Const ErrorNone     As Long = 0 
    Const ErrorNotFound As Long = -1

    Dim Result  As Long
		
    If NoOverwrite = True Then 
        ' Page or file should not be overwritten. 
        ' Check that the local file exists. 
        If Dir(LocalFileName, vbNormal) <> "" Then 
            ' File exists. Don't proceed. 
            Exit Function 
        End If 
    End If     
 
    ' Download file or page. 
    ' Return success or error code. 
    Result = URLDownloadToFile(0, Url & vbNullChar, LocalFileName & vbNullChar, BindFDefault, 0)   
 
    If Result = ErrorNone Then 
        ' Page or file was retrieved. 
        ' Check that the local file exists. 
        If Dir(LocalFileName, vbNormal) = "" Then 
            Result = ErrorNotFound 
        End If 
    End If   
 
    DownloadFile = Result 
 
End Function


With this method, the downloaded path to the image will need to be stored in the database to allow the image to be looked up.  Your form or report will then be able to display the pictures, even if the user is offline from the Internet. Also, if the pictures are planned to be used elsewhere - for example as attachments to e-mails - any picture can easily found as the path is stored in the database.

In case the source files are not supposed to change, you can set the parameter NoOverwrite  to True . Then the local copies will not be overwritten, and time will be saved because the source files will not be searched for.


Scenario 2 - Cache and Display

But what if you really don't care for storing the picture files, file names, folders, or network shares and just want the pictures to be displayed?


Then the sister API call comes in dandy: URLDownloadToCacheFile.  This uses the internet cache of Windows and Internet Explorer, which means that the retrieved picture file is saved and cached like any other file Internet Explorer would retrieve to display a web page.


It can be used like this:

Option Compare Database
Option Explicit

' API declarations.
'
Private Declare Function URLDownloadToCacheFile Lib "Urlmon" Alias "URLDownloadToCacheFileA" ( _
    ByVal pCaller As Long, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal cchFileName As Long, _
    ByVal dwReserved As Long, _
    ByVal lpfnCB As Long) _
    As Long


' Download a file or a page with public access from the web as a cached file of Internet Explorer.
' Returns the full path of the cached file if success, an empty string if not.
'
' Examples:
'
' Download a file:
'   Url = "https://www.codeproject.com/script/Membership/ProfileImages/%7Ba82bcf77-ba9f-4ec3-bbb3-1d9ce15cae23%7D.jpg"
'   Result = DownloadCacheFile(Url)
'   Result -> C:\Users\UserName\AppData\Local\Microsoft\Windows\INetCache\IE\B2IHEJQZ\{a82bcf77-ba9f-4ec3-bbb3-1d9ce15cae23}[2].png
'
' Download a page:
'   Url = "https://www.codeproject.com/Tips/1022704/Rounding-Values-Up-Down-By-Or-To-Significant-Figur?display=Print"
'   Result = DownloadCacheFile(Url)
'   Result -> C:\Users\UserName\AppData\Local\Microsoft\Windows\INetCache\IE\B2IHEJQZ\Rounding-Values-Up-Down-By-Or-To-Significant-Figur[1].htm
'
' 2017-05-25. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DownloadCacheFile( _
    ByVal Url As String) _
    As String
   
    Const BufferLength  As Long = 1024
    Const BindFDefault  As Long = 0
    Const ErrorNone     As Long = 0

    Dim FileName        As String
    Dim LocalFileName   As String
    Dim Result          As Long
   
    ' Create buffer for name of downloaded and/or cached file.
    FileName = Space(BufferLength - 1) & vbNullChar
    ' Download file or page.
    ' Return name of cached file in parameter FileName.
    Result = URLDownloadToCacheFile(0, Url & vbNullChar, FileName, BufferLength, BindFDefault, 0)
   
    ' Trim file name.
    LocalFileName = Split(FileName, vbNullChar)(0)
   
    DownloadCacheFile = LocalFileName
 
End Function

The function returns the (long) name of the cached file (see inline comments for an example) for you to use to display the image.  All you store in your database is the full URL of the image and this function does the rest and returns the file name for you to assign to a picture control.


If you run the function before opening your form or report, you can retrieve the file names of the cached files to be displayed without worrying about folder or file names. As the files are cached, you can run the report and/or browse the form multiple times with only one  download of the pictures.

Should the user or some clean-up procedure clear the cache, all that happens is that, when the form or report is opened again, the picture files will once more be downloaded and cached.


Further, when the pictures are cached, the form or report can still be displayed should the user or the source go off-line.


Leave the dirty work to the form or report

For both of the above scenarios, specifically the latter, you can actually let the form or report handle the download automatically before it is shown to the user.


To do so, recall that in an Access query you can use UDFs (User Defined Functions). Typically, these are implemented to carry out various custom calculation tasks that are impossible or too cumbersome to implement in pure SQL.


However, a UDF can do any task that a function can do, including if you need to have some action for every record, which is what we're after here: Download a picture for every record.


Now, a URL is a string like "http://www.example.com/images/somepicture.png". Unfortunately, if you save this as a hyperlink  field in a table, it is saved behind the scene as " #http://www.example.com/images/somepicture.png#".


This must be taken care of, if you retrieve the URL directly from a hyperlink field. Luckily, there is a native function of Access to handle this:

CleanUrl = HyperlinkPart(SavedUrl, acAddress)


Armed with this, we can create a small helper-function:

' Download (picture) file from a URL of a hyperlink field to a
' (temporary) folder, and return the full path to the downloaded file.
'
' This can be used as the control source for a bound picture control.
' If no Folder is specified, the user's IE cache folder is used.
'
' Typical usage in the RecordSource for a form or report where Id is
' the unique ID and Url is the hyperlink field holding the URL to
' the picture file to be displayed:
'
'   - to a cached file where parameter Id is not used:
'
'   Select *, UrlContent(0, [Url]) As Path From SomeTable;
'
'   - or, where Id is used to create the local file name:
'
'   Select *, UrlContent([Id], [Url], "d:\somefolder") As Path From SomeTable;
'
' Then, set ControlSource of the bound picture control to: Path
'
' 2017-05-28. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function UrlContent( _
    ByVal Id As Long, _
    ByVal Url As String, _
    Optional ByVal Folder As String) _
    As Variant

    Const NoError   As Long = 0
    Const Dot       As String = "."
    Const BackSlash As String = "\"
   
    Dim Address     As String
    Dim Ext         As String
    Dim Path        As String
    Dim Result      As String
   
    ' Strip leading and trailing octothorpes from URL string.
    Address = HyperlinkPart(Url, acAddress)
    ' If Address is a zero-length string, Url was not wrapped in octothorpes.
    If Address = "" Then
        ' Use Url as is.
        Address = Url
    End If
   
    If Folder = "" Then
        ' Import to IE cache.
        Result = DownloadCacheFile(Address)
    Else
        If Right(Folder, 1) <> BackSlash Then
            ' Append a backslash.
            Folder = Folder & BackSlash
        End If
   
        ' Retrieve extension of file name.
        Ext = StrReverse(Split(StrReverse(Address), Dot)(0))
        ' Build full path for downloaded file.
        Path = Folder & CStr(Id) & Dot & Ext
       
        If DownloadFile(Address, Path) = NoError Then
            Result = Path
        End If
    End If
   
    UrlContent = Result
   
End Function


So all that is needed for the form or report to automatically download and cache any given picture file, is to change the record source from, say:

Select * From SomeTable;

to:

Select *, UrlContent(0, [Url]) As Path From SomeTable;


And to save the images permanently (versus caching) in a predefined folder, just pass the folder to the function as the third argument (see inline code comments):

Select *, UrlContent([Id], [Url], "d:\somefolder") As Path From SomeTable;


Cache Example

This is an example report utilising the Cache:


and a form:


You may notice the identical cached file name is used for the form and the report.  The file has only been downloaded once and the same image is retrieved from the cache.


Online report or form

As you can see, either a form, a report or both can be turned into what the user will experience as an online form or report. The pictures will be retrieved silently before the form or report is displayed.


Further reading

Full code and samples of both reports, a normal form, and a continuous form is attached for Access 2016

PictureUrl.zip

Also, the updated code can be found at GitHub : VBA.PictureUrl

All pictures are courtesy of: World Flag Database 

5
Comment
[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
1 Comment
 
LVL 22
excellent article, gustav -- very thorough. Thank you.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month