Clean-up linked image files on record deletes

One of the benefits of storing images in database tables is that the database engine looks after things like relational integrity and deletions - if a record is deleted then an image stored in that record is also deleted. If the images are stored as external files the job of deleting the corresponding image file when a record is deleted falls to the programmer. Failure to do so will result in 'orphaned' image files.

Users can delete records in a form by using the record selector (see figure below) then pressing delete, or by using the 'Delete Record' menu item.

Record Selectors

In both cases a confirmation dialog appears:

Delete Confirmation Dialog

Obviously, we wouldn't want to delete the image file unless the user chooses 'Yes'. Conveniently, Access provides us with a form-event after the confirmation dialog is dismissed: 'Form_AfterDelConfirm'. Unfortunately, when this event reaches us the record data is already gone (if the user chose 'Yes'), so we can't identify which file to delete. The solution is to store the filename or Id during the 'Form_Delete' event (which fires before the confirmation), then actually delete the file in 'Form_AfterDelConfirm'.

Option Explicit

Dim DeleteFilename As String

Private Sub Form_Delete(Cancel As Integer)
    ' Record delete about to occur - save the filename so we can delete
     ' the file if the delete is confirmed
    If Len([Filename]) > 0 Then
        DeleteFilename = [Filename]
    Else
        DeleteFilename = ""
    End If
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
    If Status = acDeleteOK Then
        If Len(DeleteFilename) > 0 Then
            Dim DeleteImgPath As String
            DeleteImgPath = GetImagePath & DeleteFilename

            ' Delete the image file if it exists
            If Dir(DeleteImgPath) <> vbNullString Then Kill DeleteImgPath
        End If
    End If

    DeleteFilename = ""
End Sub

This code assumes that the 'FileName' field contains the image filename, and that 'GetImagePath' returns the path to the image files. See Store Images Using Relative Paths for more info.

Note that there remain various ways that a user could delete records that cannot be trapped in order to delete the corresponding image file, for example by opening a table directly and deleting the record, or running a delete query. To avoid this the application needs to be carefully locked-down to prevent such access.

Imaging for Access that's Easy, Efficient & Fast

NO OLE Bloat

NO App Dependencies

NO Complex Coding

NO Performance Penalty

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.