In certain cases I have used temporary tables in order to speed up reports.  In these cases, the queries are usually complex and the backend and / or front end are being run in a multiuser environment with multiple users accessing the reports at any given time.  The solution used was to:

1. Generate a temporary table name randomly so it would not collide with another user’s report data
2. Put the table name into a garbage table for later deletion
3. Use SELECT INTO to create the table from the report query
4. Set the report to use the temporary table.

The order of these tasks is important as well.

First, this function was placed in a module in the VBE window:

Public Function GenerateRandomTable(qdf As QueryDef) As String
    Dim strTableName, strSQL As String
    Dim qdfCreate As QueryDef
    '  Initialize temporary table name
    Randomize Timer
    strTableName = "tbl" & Int(Rnd() * 1000000000)
    ' Pull parameters for temporary QueryDef for making temp table
    ' This is for a parameter based cross tab query
    strSQL = Left(qdf.SQL, InStr(qdf.SQL, ";") + 1)
    strSQL = strSQL & "SELECT * INTO " & strTableName & " FROM " & qdf.Name & ";"
    Set qdfCreate = CurrentDb.CreateQueryDef("", strSQL)
    ' Note: Report calling form needs to be open when running this code
    qdfCreate.Parameters("Forms!frmMain!subMain!ReportForm!Supplier") _
        = CStr(Forms!frmMain!subMain!ReportForm!Supplier)
    qdfCreate.Parameters("Forms!frmMain!subMain!ReportForm!BeginningDate") _
        = Forms!frmMain!subMain!ReportForm!BeginningDate
    qdfCreate.Parameters("Forms!frmMain!subMain!ReportForm!EndingDate") _
        = Forms!frmMain!subMain!ReportForm!EndingDate
    ' This will create the new table
    qdfCreate.Execute
    ' And now return the new table name
    GenerateRandomTable = strTableName
End Function

The function takes a QueryDef from DAO as a parameter and uses that QueryDef object to build the SELECT INTO statement along with the parameters in the query.  This code specifically sets some query parameters as well.  You should generalize this code to work for your needs as it is very specific to the query I was using in this case.

The parameters are being pulled from an open criteria form, so that form must be open for this function to work.

The temporary table is given a random name consisting of “tbl” followed by 10 random digits.  I am not double checking to see if the table actually created a duplicate.  This is very unlikely, but is possible, so you may also wish to add code to the routine to make sure the table name isn’t already in use.

In the Report Open routine, I am using this code to create the table, get the name, and set the report recordsource.  This must be done in the Open routine because otherwise the recordsource becomes read-only as it is in use.

    tmpTable = GenerateRandomTable(qdf)
    Report.RecordSource = tmpTable

I do not add the table name to the garbage collection table until the Report is being closed.  In that way, we prevent the data table from being deleted while the report is in use.

Here is the CREATE TABLE statement to build the very simple table used in garbage collection:

    CREATE TABLE [tmpGarbageCollector] ([NameOfTable] TEXT (13));

And here is the CollectGarbage subroutine also put into a module in the VBE.

Public Sub CollectGarbage()
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("tmpGarbageCollector")
    While Not rs.EOF
        If TableExists(rs!NameOfTable) Then
            CurrentDb.TableDefs.Delete (rs!NameOfTable)
        End If
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
    CurrentDb.Execute ("DELETE * FROM tmpGarbageCollector")
End Sub

It goes through the whole table, makes sure the target table exists and if so, deletes it. Then it erases all the records from the tmpGarbageCollector table.

There is another function called TableExists that the garbage collector is using.  It is defined as such:

Public Function TableExists(TableName As String) As Boolean
    Dim strTablenameCheck
    On Error GoTo ErrorCode
 
    strTablenameCheck = CurrentDb.TableDefs(TableName)
    TableExists = True
 
ExitCode:
    On Error Resume Next
    Exit Function
 
ErrorCode:
    Select Case Err.Number
        Case 3265 'Item not found in this collection
            TableExists = False
            Resume ExitCode
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
            Resume ExitCode
    End Select
End Function

The code used in the Report Close routine calls the CollectGarbage subroutine, then adds its own temp table name to the table so it can be deleted later.  A globally scoped variable on the form stores the temp table name for the duration that the report is open.

    Call CollectGarbage
    CurrentDb.Execute "Insert Into tmpGarbageCollector(NameOfTable) Values('" & tmpTable & "')"

In the program’s main menu I also call the CollectGarbage routine when initially opening the app and before closing it using the Open and Close events of the form.