Microsoft Access VBA class to create a temporary table and automatically delete it upon release

DAOTemporaryTableController.cls

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "DAOTemporaryTableController"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Compare Database
Option Explicit

Public TableName As String
Private myDAOQueryRunner As DAOQueryRunner ' Source: https://gist.github.com/mjdescy/6c89c5969c3618e220814c6d04f6e449

Sub Class_Initialize()
    Set myDAOQueryRunner = New DAOQueryRunner
    TableName = "Temp"
End Sub

Sub Class_Terminate()
    Call DropTable
    Set myDAOQueryRunner = Nothing
End Sub

Public Sub OutputQueryToTemporaryTable(pQuery As String)
    Dim ModifiedQuery As String
    
    ModifiedQuery = ModifyQueryToInsertIntoTableClause(pQuery, TableName)
    Call myDAOQueryRunner.ExecuteActionQuery(ModifiedQuery)
End Sub

Private Function ModifyQueryToInsertIntoTableClause(pQuery As String, pTableName As String) As String
    ' Note: Complex queries with more than one "FROM" in them are not handled correctly by this function.
    ModifyQueryToInsertIntoTableClause = Replace(pQuery, "FROM", BuildIntoTableClause(pTableName) & "FROM", Count:=1)
End Function

Private Function BuildIntoTableClause(pTableName As String) As String
    BuildIntoTableClause = "INTO [" & pTableName & "] " & vbNewLine
End Function

Private Sub DropTable()
    Call myDAOQueryRunner.ExecuteActionQuery(BuildDropTableQuery())
End Sub

Private Function BuildDropTableQuery() As String
    BuildDropTableQuery = "DROP TABLE [" & TableName & "]"
End Function

Access does not allow you to use a subquery in a SQL JOIN condition. If you wish to do so, and are using VBA to execute the queries, you can use this class to create and use a temporary table instead. Once this class goes out of scope, its associated temporary table will be dropped.