[Webinar] Streamline your web hosting managementRegister Today


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
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.


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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

590 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