MS Access Database (2010) How to Create Temporary Table / Procedure / View from Query Designer

Although this question has been answered over the course of a year, I hope the following approach might be helpful to others who, like me, are trying to pragmatically compensate for the shortcomings of MS Access and want to focus on real sql queries.

It covers:

• easy-to-read sql procedures stored in text files works (the general solution to coding them in VBA with concatenated strings becomes rather awkward and complicated)

• using temporary tables. Note that this solution does not prevent accdbs from bloating - so they still need to be compressed from time to time. This can be prevented if you choose to run your procedure on a temporary database, which requires some additional coding and error handling.

(I use an empty accdb with the following code as templates and then work from there, for example if additional VBA routines or forms are required.)

Steps:

1 - Save the SQL statement or entire procedures in txtfile. To keep VBA shortcode it needs

• Sql statements that must be closed with a semicolon; followed by the line

• commented lines to cover the entire line, which means no extra inline characters before / * and / or after * /

OP obv. has access to sql editor, others don't. But: even in a company environment, you can usually get access to some free text editor with SQL highlighting (like notepad ++ , which emphasizes syntax in .sql files).

2 - Calling a subroutine

Sub SubRunSQL()
On Error Resume Next
droptemptables
On Error GoTo 0

runSQL CurrentProject.path & "\_sql\p01.sql"

End Sub

      

        
        
        
      

    

3 - Closing + deleting temporary tables (in my case labeled "tmp", bc Access has problems with the # character in table names, namely: tablename needs to be put in [brackets] in sql, which leads to problems when deleting these tables in cycle).

Putting on "resume next" is everything but elegance, but in my case it turned out to be the most reliable solution (the goal is to code real SQL, not some interface around it.)

Sub droptemptables()
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If tdf.Name Like "tmp*" Then
        DoCmd.Close acTable, tdf.Name, acSaveNo
        DoCmd.DeleteObject acTable, tdf.Name
    End If
Next tdf
End Sub

      

        
        
        
      

    

4 - fetching an SQL procedure and processing single statements in a loop.

Sub runSQL(path As String)

Dim dbs As DAO.Database
Set dbs = CurrentDb

Dim sql As String
sql = fetchSQL(path)

' optional: replace some string in your textfile with a public declared variable (here: Public Const dwh As String = "'I:\mydwh.accdb'")
sql = Replace(sql, "#dwh", dwh)

' Split at semicolon + carriage left
Dim arrsql() As String
arrsql() = Split(sql, ";" & vbNewLine)

' Loop through the SQL-Statements
Dim i As Integer
For i = LBound(arrsql()) To UBound(arrsql())
    Debug.Print arrsql(i)
    dbs.Execute arrsql(i), dbFailOnError
Next i
End Sub

      

        
        
        
      

    

5 is a standard function to return the contents of a text file as a string. In this case, I also skip the commented lines, which is redundant if you choose not to comment out your sql.

Function fetchSQL(path As String) As String

Dim skpLine As Boolean
Dim Line As String
Dim iFile As Integer
iFile = FreeFile
Open path For Input As #iFile

' skip commented lines /* ... */
Do Until EOF(iFile)
    Line Input #iFile, Line
    If Left(Line, 2) = "/*" Then skpLine = True
    If skpLine = False Then fetchSQL = fetchSQL & vbNewLine & Line
    If Right(Line, 2) = "*/" Then skpLine = False
Loop
Close #iFile
End Function