Code Library

Automatically Cleaning Up Temporary Files on Program Exit

A dead-simple way to clean up temporary files without having to worry about waiting until they are no longer in use.

Mike Wolfe

Dec 22, 2021 • 5 min read

Nothing is so permanent as a temporary government program.

- Milton Friedman

If you're not careful, temporary files created in VBA may be equally permanent.

A Typical Situation

Here's an example. Let's say I want to create a report where I'm superimposing data on top of a government form. I have an image of the government form. I am going to use an Access image control to display the government form on my Access report.

If I embed the image in the control, it will bloat the size of the Access front-end.

Instead of embedding the image, I can link the image in the control. One problem with this is that I need to store the image somewhere that every user will have access to it. If I store it on a network folder, I have to ensure that no overzealous IT administrator will come through and clean up this file (especially since it will probably appear to be unused).

I deal with this situation by embedding the raw binary of the file into a local table in the front end. When the report opens, I extract the raw binary into an image file that I store in the user's temp directory. I then link to the image's full path from my report's image control.

One problem with this approach is that I can't delete the temporary image file until the report is closed. Doing the deletion from the report's On Close or On Unload event is unreliable. If Access has not released the lock on the image file, the delete will fail. Checking for and managing these types of race conditions is a tedious and error-prone endeavor.

I've devised a method to avoid the race condition problem (almost) entirely.

The RegisterTempFile Method

I have a global singleton class that gets created on-demand via a publicly declared global variable (I have written about this class in the past):

Public App As New clsApp

When I create a temporary file, I call a method in the clsApp class module to register the file in a class-level collection variable: App.RegisterTempFile FullPathToTempFile.

'--== clsApp class module ==--
Option Explicit
Option Compare Database

'The following line goes in the class header section:
Private m_collTempFiles As Collection


'---------------------------------------------------------------------------------------
' Purpose   : Register a temporary file for cleanup.
' Notes     - The full path to the file must be passed to avoid accidentally deleting the wrong thing.
'           - These files are cleaned up when this class Terminates, usually at program exit.
'           - Changing data sources will also force a cleanup, because clsApp gets re-instantiated.
'---------------------------------------------------------------------------------------
'
Public Sub RegisterTempFile(fpTempFile As String, Optional IgnoreTempFolderCheck As Boolean = False)
    'Validate the data
    If Len(fpTempFile) = 0 Then Exit Sub

    Dim IsFullPath As Boolean
    IsFullPath = (Left(fpTempFile, 2) = "\\") Or (Mid(fpTempFile, 2, 2) = ":\")
    If Not IsFullPath Then Throw "Not a full path: {0}", fpTempFile

    If IsDev() And Not IgnoreTempFolderCheck Then
        'File should be in the temp folder
        Dim IsInTempFolder As Boolean
        Dim foTemp As String  'temp folder
        foTemp = Environ("TEMP")
        IsInTempFolder = fpTempFile Like foTemp & "*"
        If Not IsInTempFolder Then Throw "Temporary files, like {0}, should be saved in the temporary folder: {1}", fpTempFile, foTemp
    End If

    If m_collTempFiles Is Nothing Then Set m_collTempFiles = New Collection

    m_collTempFiles.Add fpTempFile
End Sub

Private Sub Class_Terminate()
    If Not m_collTempFiles Is Nothing Then DeleteTempFiles
End Sub

'---------------------------------------------------------------------------------------
' Purpose   : Removes temporary files created by this program and registered via RegisterTempFile.
'---------------------------------------------------------------------------------------
'
Private Function DeleteTempFiles()
    On Error Resume Next
    Dim fpTempFile As Variant
    For Each fpTempFile In m_collTempFiles
        'Extra safeguard to help prevent catastrophe
        Dim HasWildcard As Boolean
        HasWildcard = (InStr(fpTempFile, "*") > 0) Or _
                      (InStr(fpTempFile, "?") > 0)
        If Not HasWildcard Then
            SetAttr fpTempFile, vbNormal  'Remove read-only attribute if it is set
            Kill fpTempFile
        End If
    Next fpTempFile
End Function

Usage

The beauty of this approach is that I only have to remember to register the temporary file at the moment I create it.

Dim MyTempFilePath As String
MyTempFilePath = TempFileName()   'https://nolongerset.com/tempfilename/

App.RegisterTempFile MyTempFilePath

'https://nolongerset.com/text-files-read-write-append/
FileWrite MyTempFilePath, "Some text for my file"

When Access closes, the text file saved at MyTempFilePath will be deleted by the App class's OnTerminate() event handler.

This is very easy to do and it requires almost no thought on the part of the programmer. The class eliminates most sources of race conditions (it can't eliminate all of them as you'll see below).

This method is an example of making it so The Right Way is also The Easy Way.

Dependencies

The code above comes from my global singleton class, clsApp.

It makes use of my Throw() method to easily raise custom errors.

It uses my IsDev() function to add an extra check when running in developer mode to encourage creating temporary files within the user's system-designated temporary folder. (Technically, it's the "TEMP" environment variable-designated temporary folder. In practice, these will generally be the same thing. And, since the check only runs in developer mode anyway, it's not worth worrying about whether the user has modified their local environment variables.)

Additional Notes

Defensive programming safety measures

The Kill method honors wildcard characters. Because I'm paranoid, I won't use this automatic cleanup method to delete files based on a wildcard pattern. I don't want some idiot (like me), doing something silly like calling App.RegisterTempFile "C:\My\Super\Important\Folder\*" and accidentally deleting all the stuff in My Super Important Folder.

Errors ignored

Since this code usually runs as the program is exiting, error trapping gets dicey. I decided it wasn't worth bothering the user if something went wrong with my temporary file clean up. The worst case scenario is that there is a temporary file that gets left behind even after the program exits.

This could even be a situation beyond our control. Say we create a temporary .csv file to act as the data source for a Word mail merge document. If Word is still open when we close out of Access, there will still be a lock on the temporary .csv file. In this scenario, the Kill statement will throw an error. We'll simply ignore it.

Beware code resets

This is less of an issue for end users than it is during development.

If you register several temp files for deletion, then reset the code by clicking on the blue Reset square in VBA, then clsApp's Class_Terminate() event handler will never get a chance to run.

If you find yourself needing to reset your code, you can force the Class_Terminate event to run by calling Set App = Nothing before you click the reset button.

To be clear, you do not need to call Set App = Nothing anywhere in your code. That is something you would run from the Immediate Window just before clicking the blue Reset button.