Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Microsoft Access VBA class to create a temporary table and automatically delete it upon release
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
@mjdescy

This comment has been minimized.

Copy link
Owner Author

@mjdescy 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