Skip to content

Instantly share code, notes, and snippets.

@mjdescy

mjdescy/DAOTemporaryTableController.cls

Created May 30, 2018 15:18
Show Gist options
  • Save mjdescy/621e03e8d2dd83984d83ec83ca05c6fc to your computer and use it in GitHub Desktop.
Save mjdescy/621e03e8d2dd83984d83ec83ca05c6fc to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. mjdescy created this gist May 30, 2018.
    48 changes: 48 additions & 0 deletions DAOTemporaryTableController.cls
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,48 @@
    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