CREATE TEMPORARY TABLE ...problem

[matthew]

Hello
i need to have a temporary table to use to store data entered throguh a form
I have tried those two SQL statements and both didn't work
"CREATE TEMPORARY TABLE...
"CREATE TABLE #tablename...
The first one is suggested in the Access Help
What can i do to create a temporary table? (and why the two statements wouldn't work...?
I'm working in AccessXP with ADO (but I have tested it also in Access2000).
Thanks
Matthew

 

[Gerald Stanley]

The syntax of CREATE TEMPORARY TABLE is wrong, it should be
CREATE TABLE. Although you can start a tablename with a #
when designing a table, the Microsoft Jet Engine objects to
the # when running a CREATE TABLE statement. So if you try
CREATE TABLE tablename, you should be ok.

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Hello,
i need to have a temporary table to use to store data entered throguh a form.
I have tried those two SQL statements and both didn't work:
"CREATE TEMPORARY TABLE..."
"CREATE TABLE #tablename..."
The first one is suggested in the Access Help.
What can i do to create a temporary table? (and why the

two statements wouldn't work...?)

 

[Chris2]

The syntax of CREATE TEMPORARY TABLE is wrong, it should be
CREATE TABLE. Although you can start a tablename with a #
when designing a table, the Microsoft Jet Engine objects to
the # when running a CREATE TABLE statement. So if you try

"#" is an illegal character for Access object names. When using CREATE
TABLE (or other SQL statements), enclosing a name in [] will allow the "#"
to work, but it shouldn't be used at all. (The various Design Views
actually add in the [] for the user, hiding the fact that "#" shouldn't be
used.)
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.