Trying to Create a Temporary Table in ACCESS/SQL

Posted on 2003-03-06
Medium Priority
Last Modified: 2008-02-01
Access 2000 & SQL Server 7.0 (I have also put this question into the SQL Section)

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 behind a from to mangle data in.

I am issuing the code from behind an Access Form 'passing' the table to a Report.

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

The syntax according to SQL should prefix the table name with a '#', 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.

So I am stuck here help would be appreciated. (Of course if anyone can tell me how to make a temporary table by not using SQL commands that would be good too.)

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
  • 2
  • 2
LVL 44

Accepted Solution

Arthur_Wood earned 300 total points
ID: 8079388
Access DOES NOT support the concept of Temporary Tables.  Temporary tables are supported in SQL Server, and Oracle, but not in Access.


Expert Comment

ID: 8080098
Just create a permanent table and delete it when you're done with it using DROP TABLE.

Author Comment

ID: 8088690
Thank you this explains a lot. (It would be helpful if the Access Help told you this... ah well).

I had already come with the method of removing the table (thanks for the idea though)

Expert Comment

ID: 8088753
I work primarily in Access 97, where temporary tables are not mentioned at all.  I peeked at the Access 2000 help file and it says they are supported--which I find a bit confusing based on your experience.  Wierd.
LVL 44

Expert Comment

ID: 8091535
kraig, they are probably 'supported' on the Latest version of MSDE, which is really just a stripped down SQL Server - and THEY ARE supported, of course, in SQL server.

Just a thought.


Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

762 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