MS Access database (2010) how to create temporary table/procedure/view from Query Designer

Question

Is there any way how to create temporary table/view/stored procedure in MS Access database (2010) using Query Designer?

Whenever i try to execute something like this:

SELECT * INTO #temp_table
FROM (SELECT column_1, column_2 FROM table)

MS Access throws an error:

Syntax error in CREATE TABLE statement.

With no further information.

Solifluction

Answer 1

Access SQL does not support executing more than one SQL statement at a time, so the notion of a #temp table does not really apply. If you require a temporary table then you need to

• create a "real" table,
• do your work with it, and then
• delete (drop) the table when you're done.

Samarskite

Answer 2

While this has been answered for a year, I hope the following approach might be helpful to others who are, like me, trying to compensate for MS Access shortcomings in a pragmatic way and want to focus on the actual sql-queries.

It covers:

• running easy to read sql-procedures stored in textfiles (the common solution of coding them in VBA with concatenated strings becomes unhandy and difficult rather soon)

• using "temporary" tables. Note that this solution does not prevent accdbs from "bloating up" - so they still have to be compressed from time to time. This can be prevented if you chose to run your procedure in 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, e.g. if additional VBA-Routines or Forms are required.)

Steps:

1 - Store your SQL-statement or whole procedures in a txtfile. In an attempt to keep the VBA-code short it requires

• sql statements to be closed with a semicolon ; followed by a linebreak

• commented lines to span the whole line, meaning no additional inline characters before the /* and or after the */

The OP obv. has access to an sql-editor, others might not. But: even in a company environment you should usually be able to get your hands on some freeware texteditor with SQL-highlighting (like notepad++ which highlights syntax in files with .sql-extensions).

2 - Calling the routine

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

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

End Sub

3 - Closing + deleting the "temporary" Tables (in my case labeled with a prefix "tmp", bc Access has issues with the # character in table names - namely: tablename has to be put in [brackets] in the sql, which results in issues when deleting those tables in a loop).

Wrapping it in "resume next" is everything but elegant, but in my case it proved the most robust solution (the goal being the coding of the actual SQL-Code, not some UI 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 the SQL-Procedure and processing the 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 - A standard function for returning the contents of a textfile as string. In this case, i also skip commented lines, which is redundant if you chose not to comment 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

Parrott

an alternative approach that requires some more VBA might be to use csv files (+auto genereated schema.ini files) as temporary tables - performance might be superslow with larger files, but it prevents the accdb from bloating – Parrott

Answer 3

As far as I know there is no way to execute a SQL statement like the one in your question in the Access Query Designer, as it expects every statement to be a View/Procedure or Function definition.

There is a general problem with you statement. When using a subselect/derived table within your FROM clause, you will have to use an alias for the returned results. This will work in SQL Server Management Studio:

SELECT * INTO #temp_table
FROM (SELECT column_1, column_2 FROM table) AS someAlias

I recommend using SQL Server Management Studio (or a similar tool) for most of the SQL work when developing Access Data Project (ADP) applications.

Unpile

"I recommend using SQL Server Management Studio ... for most of the SQL work when developing Access applications." - That is bad advice. There are significant differences between Access SQL and T-SQL so a query that will work in SSMS will not necessarily work in Access. – Samarskite

@Gord: Thanks for that hint. Based on the context of views+stored procedures I was assuming an Access Data Project. I clarified my recommendation in that regard. – Unpile

Question itself is abou Access SQL and not about T-SQL. I know how to acomplish things i need in T-SQL, but i am trying to figure out how to make them work in Access Sql – Solifluction

Answer 4

Try this

SELECT*
INTO Temp1
FROM TableName;

Note: you should have an existing table from which you want to create your temporary table.

Use a SELECT statement to check your results:

SELECT*
FROM Temp1;

Rochet

Answer 5

This will work if you add:

SET NOCOUNT ON
SELECT * INTO #temp_table
FROM (SELECT column_1, column_2 FROM table)

Leslielesly

But MS Access does not allow multiple Select Statement – Winou