CREATE TEMPORARY TABLE ...problem

[Guest]

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.

 

[Tom Ellison]

What Chris says is true for Access Jet databases, but not for Access
MSDE databases. It is common to forget that, since 2000, Access has
had two separate database design systems, MDBs and ADPs. With ADPs, a
# on the front of a table name is EXACTLY how to specify that it is a
temporary table with a connection lifetime.

Much of the rest of what Chris says is also true only for MDBs, but
not for ADPs. Since Matthew seems to be referring to features that
are quite valid for ADPs, it would be useful to know from him in which
context he intends his question.

For those few (perhaps very few) of us who write ADPs exclusively,
referring to the MDB system of Access as though it were the ONLY
Access system makes us feel very lonely, indeed!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

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.

CREATE TABLE tablename, you should be ok.

Hope This Helps
Gerald Stanley MCSD
two statements wouldn't work...?)