Skip to content

Instantly share code, notes, and snippets.

Created May 30, 2018 15:18
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?
Microsoft Access VBA class to create a temporary table and automatically delete it upon release
MultiUse = -1 'True
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:
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
Copy link

mjdescy commented May 30, 2018

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment