• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1103
  • Last Modified:

Trying to Create a Temporary Table in ACCESS/SQL

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

0
bmtech
Asked:
bmtech
  • 2
  • 2
1 Solution
 
Arthur_WoodCommented:
Access DOES NOT support the concept of Temporary Tables.  Temporary tables are supported in SQL Server, and Oracle, but not in Access.

AW
0
 
kraigCommented:
Just create a permanent table and delete it when you're done with it using DROP TABLE.
0
 
bmtechAuthor Commented:
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)
0
 
kraigCommented:
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.
0
 
Arthur_WoodCommented:
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.

AW
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now