A simple method for linking and viewing images in Access

Access has traditionally not been a good environment for storing media, images and files due to database bloating. Because of that many people prefer to store images in the file system and link to those files from Access. The approach shown below is just one of many methods, and has these features:

  • Files are stored in a predefined directory
  • The images are located / stored using a button on a subform. They are displayed in an unbound image frame on the main form.
  • The same button is used to browse for and display a new image, and to display an existing image.
  • Images are located on disk using the FileDialog object. Once selected you can open, save, rename or otherwise modify the file.

You will need to set up the following to use this code:

  • A command button on a subform, with the code shown in Listing 1.
  • A text field in the subform, named Photo
  • An unbound image frame in the main form, called imgDisplay. When you set up this control you will be prompted to browse for an image. Whatever you select will become the default image.
  • You will also need to save Listing 2 in the code module of the subform.

Listing 1

TryAgain:
    If IsNull(Me.Photo) Or Len(Me.Photo) = 0 Then
        'no image; load an image before showing
        CopyImage
        GoTo TryAgain
    Else
        Me.Parent!imgDisplay.Picture = Me.Photo
    End If

The If statement checks first to see whether the Photo field is empty. If it is the CopyImage function launches, prompting the user to locate an image for the record. Once the image is copied and the link written, the code returns to the top of the routine and loads the link into the image container.

Listing 2

Private Function CopyImage()
    ''====================================================
    ''You will need a reference to the Microsoft Office x.0 Object Library
    '' where x = 10,11,12 or 13
    ''====================================================

    Dim fd As Office.FileDialog
    Const SAVE_PATH = "H:\Documents\Images\" 'default folder for saving the images
    Dim strFname As String
    Dim i As Integer
    Dim vrtSelectedItem As Variant
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        .Filters.Clear
        
        .Filters.Add "All files", "*.*"
        .Filters.Add "Images", "*.gif; *.jpg; *.jpeg", 1
        .AllowMultiSelect = False
        
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
                strFname = ""
                For i = Len(vrtSelectedItem) To 1 Step -1
                    If Mid(vrtSelectedItem, i, 1) = "\" Then
                        strFname = Mid(vrtSelectedItem, i + 1)
                        Exit For
                    End If
                Next i
                'copy to the destination directory
                FileCopy vrtSelectedItem, SAVE_PATH & strFname
                ‘optional: delete the original, keeping only the copy.
                Kill vrtselecteditem
                ‘write the full path to the Photo field
                Me.Photo = SAVE_PATH & strFname
            Next vrtSelectedItem
            'The user pressed Cancel.
            Else
        End If
    End With
    
    'Set the object variable to Nothing.
    Set fd = Nothing
End Function

About sydneygeek

I was a consultant in Excel and Access for about 14 years before taking a full time role as an analyst with an energy company. Tech interests include Excel, Access, VBA, HTML / CSS and Oracle. I am based in Sydney, Australia and am a moderator on MrExcel, where I go by the name SydneyGeek.
Bookmark the permalink.