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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
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.