CREATE TEMPORARY TABLE

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy Schmidt - 26 Jan 2005 21:25 GMT

The help as well as the Microsoft website for MS ACCESS lists the syntax:

CREATE [TEMPORARY] TABLE...

Yet, any attempt to use the "TEMPORARY" keyword leads to a syntax error.

Is this a documentation error that Microsoft has acknowledged - or is this a
software error? Which is wrong?

Best Regards,
Andy

Reply to this Message

Tim Ferguson - 27 Jan 2005 19:27 GMT

> The help as well as the Microsoft website for MS ACCESS lists the syntax:
>
> CREATE [TEMPORARY] TABLE...
>
> Yet, any attempt to use the "TEMPORARY" keyword leads to a syntax error.

Are you using DAO or ADO? I've not come across this usage, but I don't do a
lot with ADO so I'm guessing it's Jet 4 or more.

Tim F

Reply to this Message

Andy Schmidt - 27 Jan 2005 19:56 GMT

Hi,

> Are you using DAO or ADO? I've not come across this usage, but I don't do
> a
> lot with ADO so I'm guessing it's Jet 4 or more.

I tried ADO and I even submitted the SQL statement directly from within
Access (using RUNSQL). In either case, the keyword "TEMPORARY" is recject -
even though it IS document (and elaborated on) in the JET 4.0 SQL help file
"JETSQL40.CHM" that comes with Access, and the appropriate document on the
Microsoft website.

I understand that I can manually create and drop tables within on session.
I'm just trying to find out whether this SHOULd work, e.g., if this is a
documentation bug - or if this is a "software problem".

I didn't find any knowledgebase article that states that this is a
documentation error - or otherwise.

Best Regards,
Andy

Reply to this Message

Chris2 - 27 Jan 2005 19:48 GMT

> The help as well as the Microsoft website for MS ACCESS lists the syntax:
>
[quoted text clipped - 7 lines]
> Best Regards,
> Andy

As far as MS Access .mdb databases are concerned:

Access does not support true Temporary Tables like other DBMS
products.
They can be simulated via VBA code, though (or Macros as a last
resort).
Simply create a Table via DAO in VBA (or DoCmd.RunSQL, etc.), use it,
and
when done, delete it.

I can see where, in the JETSQL40.CHM file that it shows the syntax for
the TEMPORARY keyword, and even has an explanation that defines it's
use, but I've no idea what it's doing there, since it doesn't work.

When doing .adp, I would imagine SQL Server's "CREATE TABEL
#tablename" syntax would follow, but as I don't have an SQL Server
handy, I can't test it.

Reply to this Message

Andy Schmidt - 27 Jan 2005 20:04 GMT

Hi Chris:

Thanks for the reply.

>> Access does not support true Temporary Tables like other DBMS products.
>> <<

That's what I'm suspecting as well - I've been given the task to get the
"official" word. Do you have any reference - I couldn't find any KB article
that "withdraws" the how-to information they published in their help file
and on their support web site.

>> > When doing .adp, I would imagine SQL Server's "CREATE TABEL #tablename"
>> > syntax would follow <<

Good thinking - I quickly tried the "#" syntax against JET and that is not
valid either (I know you were talking about .ADP projects and SQL server,
but I couldn't lose trying...).

Andy

Reply to this Message

Brendan Reynolds - 27 Jan 2005 20:17 GMT

I tried all the things I could think of that it seemed to me might make a
difference. I used ADO. I turned on the SQL Server compatible (ANSI 92)
option. I converted to Access 2002/2003 format. Nothing worked. I tested the
query by taking out the TEMPORARY keyword, to eliminate the possibility of
some other syntax error in the query. Worked fine without it, just wouldn't
work with it. Unless someone else knows different, it looks like a
documentation error to me.

Signature

Brendan Reynolds (MVP)

> The help as well as the Microsoft website for MS ACCESS lists the syntax:
>
[quoted text clipped - 8 lines]
> Best Regards,
> Andy

Reply to this Message

Tim Ferguson - 28 Jan 2005 17:06 GMT

> I tried all the things I could think of that it seemed to me might
> make a difference. I used ADO. I turned on the SQL Server compatible
> (ANSI 92) option. I converted to Access 2002/2003 format.

Me too: even going up straight against SQL Server with OSQL, I get this -

1> create temporary table Tim1 (
2> TimNum integer constraint pk primary key,
3> AnotherField varchar(32) null
4> )
5> go
Msg 156, Level 15, State 1, Server ALESI, Procedure , Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
keyword 'temporary'.
1>

I think the Books Online give an alternative version for creating temp
tables in SQL Server. In Jet, it's easiest just to create a new database,
make the tables in it, and erase the mdb afterwards.

All the best

Tim F

Reply to this Message