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.
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'.
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]
DeleteFilename = ""
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
DeleteFilename = ""
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.