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

ms-access ms-access-2010
Kajiyama picture Kajiyama · Apr 17, 2015 · Viewed 39k times · Source

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.

Answer

Gord Thompson picture Gord Thompson · Apr 17, 2015

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.

Related questions

How to insert values into the database table using VBA in MS access

I've started to use access recently. I am trying to insert a few rows into the database; however, I am stuck as it is throwing an error: Too few parameters. I have a table test with only one column in …

Read More
Corrupted Access .accdb file: "Unrecognized Database Format"

I'm having a problem. One of my databases on our shared network will not open for any user. It says "Unrecognized Database Format." I've had this problem before but was still able to open the database, compact and repair, or …

Read More
Access 2010 VBA query a table and iterate through results

I have a query that I want to execute against a table. With the results I want to do something. In my head the pseudo code is: var q = "select * from table where some condition"; var results = db.getResults(q); …

Read More