Cannot Create a temporary  Table in ACCESS using SQL

Posted on 2003-03-05
Medium Priority
Last Modified: 2007-12-19
Access 2000, & SQL server

I am trying to create a temporary table using the command

StSQL = 'CREATE TEMPORARY TABLE XXXXX (field1 Interger, field2 text)'

then executing it by using

cnCurrent.Execute stSQL

If I omit the word TEMPORARY this works perfectly well, except that I have to manually delete the table afterwards. However if I use the (apparently valid) syntax adding the word TEMPORARY then I get an error
"Syntax error in CREATE TABLE" which is not very helpful.

So is this valid and if so what am I doing wrong here please?

Thank you

Question by:bmtech
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 23

Expert Comment

ID: 8073122

Are you using an SQL Server database?
To create a temporary table in SQL, you need to prefix the table name with a # symbol:

StSQL = 'CREATE TABLE #YourTempTable (field1 Int, field2 VARCHAR(50))'


Expert Comment

ID: 8073139

the syntax to create a temporary table is

create table #tblTemp (....)

(# stands for temporary table)

Good luck!

Expert Comment

ID: 8073148
HI there

You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

so the syntax to create a global temporary table is create table ##table_name (field1 Interger, field2 text)

give that a try

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI


Author Comment

ID: 8073417
Okay one other thing I should clarify, I am using an MDB/MDE as a front end, data is stored in tables in SQL & Access. I want to create a working table to mangle data in.

I am issuing the code from behind an Access Form.

The Code:
stSQL = "create Table #CallHist (field1 int, field2 text)"
Set cnCurrent = CurrentProject.Connection
Set rsData = New ADODB.Recordset
cnCurrent.Execute stSQL ' Create temporary table

This gives a (ADO) syntax error with the '#' without it, it creates the table fine (but of course it permanent then. If you look at the syntax in the MSAccess Help it suggests that the syntax should be CREATE TEMPORARY TABLE xxxx. I have tried most combinations of #, ## TEMPORARY and so on that I can think of. I have seen the SQL syntax that you are listing here but I cant get it to work.

So I am stuck here.
LVL 23

Expert Comment

ID: 8073865
Might be worth adding a link to this question in the MS Access section
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 300 total points
ID: 8087778
Access doesn't have temporary tables. The keyword TEMPORARY is used in ORACLE, while the # prefix is for SQL Server.

Author Comment

ID: 8088713
Thanks for the concise answer. I have just found this out in the Access Forum too, but its nice to have it confirmed. I will come up with another way of doing this.

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question