CREATE TEMPORARY TABLE

according to the help, its possible to create temporary tables that are
automatically deleted at the the end of a session/connection but i cant
get this to work. The CREATE TEMPORARY TABLE always causes a syntax
error. This is straight to Jet not a passthru.

Is the help wrong ?

dave

I'm sure Help was referring to SQL Server tables in an ADP, not JET in an
MDB.
--
Duane Hookom
MS Access MVP

"David Rigler" wrote in message
...

according to the help, its possible to create temporary tables that are
automatically deleted at the the end of a session/connection but i cant
get this to work. The CREATE TEMPORARY TABLE always causes a syntax error.
This is straight to Jet not a passthru.

Is the help wrong ?

dave

David Rigler wrote in news:uS$V14LhGHA.4848
@TK2MSFTNGP04.phx.gbl:

. The CREATE TEMPORARY TABLE always causes a syntax
error. This is straight to Jet not a passthru.

Not very helpful answer: I'm not aware of this syntax and I've never
tried it.

Slightly more helpful answer: creating and destroying Access objects
tends to create mdb file bloat and is a big risk for file corruption. If
you need a temporary table, it's a much better idea to create a temporary
mdb, make the table there, and delete the whole thing afterwards.

Really helpful answer (ok, not so helpful as this is air code, but it
should get you started):

' make a new empty table
jetSQL = "CREATE TABLE MyTemp( " & vbNewLine & _
" MyNumber INTEGER NOT NULL, " & vbNewLine & _
" MyString TEXT(32) NULL " & vbNewLine & _
");"

' get somewhere to store it
databaseFilePath = GetTempFilePath()

' references to DAO, can do in ADOX/ADODB if you prefer
set db = dbengine.Workspaces(0).CreateDatabase( _
databaseFilePath, _
dbLangGeneral, dbVersion30)

' create the table in the new database
db.Execute jetSQL, dbFailOnError

' do something with the table here
' all finished?

' drop the connection
db.Close

' and get rid of the disk file
Kill databaseFilePath

Hope that helps


Tim F

As others have pointed out, this is a feature of SQL Server, not of Jet.
Are you positive that you need a table? Queries and tables are
interchangeable for most purposes.

"David Rigler" wrote in message
...

according to the help, its possible to create temporary tables that are
automatically deleted at the the end of a session/connection but i cant
get this to work. The CREATE TEMPORARY TABLE always causes a syntax error.
This is straight to Jet not a passthru.

Is the help wrong ?

dave

Tim Ferguson wrote:
David Rigler wrote in news:uS$V14LhGHA.4848
@TK2MSFTNGP04.phx.gbl:

. The CREATE TEMPORARY TABLE always causes a syntax
error. This is straight to Jet not a passthru.

Not very helpful answer: I'm not aware of this syntax and I've never
tried it.

Slightly more helpful answer: creating and destroying Access objects
tends to create mdb file bloat and is a big risk for file corruption. If
you need a temporary table, it's a much better idea to create a temporary
mdb, make the table there, and delete the whole thing afterwards.

Really helpful answer (ok, not so helpful as this is air code, but it
should get you started):

' make a new empty table
jetSQL = "CREATE TABLE MyTemp( " & vbNewLine & _
" MyNumber INTEGER NOT NULL, " & vbNewLine & _
" MyString TEXT(32) NULL " & vbNewLine & _
");"

' get somewhere to store it
databaseFilePath = GetTempFilePath()

' references to DAO, can do in ADOX/ADODB if you prefer
set db = dbengine.Workspaces(0).CreateDatabase( _
databaseFilePath, _
dbLangGeneral, dbVersion30)

' create the table in the new database
db.Execute jetSQL, dbFailOnError

' do something with the table here
' all finished?

' drop the connection
db.Close

' and get rid of the disk file
Kill databaseFilePath

Hope that helps


Tim F






thanks for that

dave

Pat Hartman(MVP) wrote:
As others have pointed out, this is a feature of SQL Server, not of Jet.
Are you positive that you need a table? Queries and tables are
interchangeable for most purposes.

"David Rigler" wrote in message
...
according to the help, its possible to create temporary tables that are
automatically deleted at the the end of a session/connection but i cant
get this to work. The CREATE TEMPORARY TABLE always causes a syntax error.
This is straight to Jet not a passthru.

Is the help wrong ?

dave



I guess it is for SQL Server, although the help doesnt make that
obvious. I'm importing some SQL server code which uses temp tables and
dont want to make to many changes.

thanks

dave

Tim Ferguson wrote:
Slightly more helpful answer: creating and destroying Access objects
tends to create mdb file bloat and is a big risk for file corruption.

Is this known to be true for tables?

I've had the same mdb file for years which I use to test all my Jet SQL
code. I must have run literally thousands of SQL DDL statements via ADO
of the CREATE/ALTER/DROP TABLE/VIEW/PROCEDURE/CONSTRAINT/INDEX family.
I compact the file probably less frequently than every six months
because I always have to find the JRO code to do it. The mdb file,
around 100mb, never seems to recover more than about 3%.

Any idea what's going on here? I'm tempted to conclude that CREATE/DROP
TABLE does not cause much file bloat but there could be some other
factor (my file is bloated beyond repair, JRO is rubbish, etc).

TIA.

create a temporary
mdb, make the table there, and delete the whole thing afterwards.

File bloat aside, this may be a good idea in its own right. For
example, there may not be CREATE/DROP permissions available for the
production database but it's almost always possible to write a new file
to the current user's temp folder.

It's worth pointing out that a (temp) table is more often than not used
to join to other tables in the production database and creating it in a
separate file may add significant performance overhead.

Also, if the operation is not a one off exercise, the OP should
consider promoting the temp table to a permanent auxiliary table.

Jamie.

--

"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in
:

I'm sure Help was referring to SQL Server tables in an ADP, not JET in
an MDB.

In that case, you have to prefix the table name with a # -- this comes from
the SQL Books Online definition for CREATE TABLE. It works for me.

table_name
Is the name of the new table. Table names must follow the rules for
identifiers. table_name can be a maximum of 128 characters, except for
local temporary table names (names prefixed with a single number sign (#))
that cannot exceed 116 characters.


HTH

TimF

Access queries are the equivalent of temp tables in SQL Server. I don't
know what the code is doing but you can open a recordset based on a query
and that is the equivalent of creating a temp table and then opening a query
based on the temp table in SQL Server. If you are trying to provide a
recordset for a form or report, just use the query as the RecordSource
rather than a table.

"David Rigler" wrote in message
...
Pat Hartman(MVP) wrote:
As others have pointed out, this is a feature of SQL Server, not of Jet.
Are you positive that you need a table? Queries and tables are
interchangeable for most purposes.

"David Rigler" wrote in message
...
according to the help, its possible to create temporary tables that are
automatically deleted at the the end of a session/connection but i cant
get this to work. The CREATE TEMPORARY TABLE always causes a syntax
error. This is straight to Jet not a passthru.

Is the help wrong ?

dave



I guess it is for SQL Server, although the help doesnt make that obvious.
I'm importing some SQL server code which uses temp tables and dont want to
make to many changes.

thanks

dave

Pat Hartman(MVP) wrote:
Access queries are the equivalent of temp tables in SQL Server.

I would say the that Access/Jet Query objects are equivalent to VIEWs
in SQL Server (or any other DBMS). Consider that Access/Jet's CREATE
VIEW syntax
(http://office.microsoft.com/en-us/as...322231033.aspx)
creates a Query object in the database.

you can open a recordset based on a query
and that is the equivalent of creating a temp table and then opening a query
based on the temp table in SQL Server.

I don't understand. When I've used a temp table in SQL Server I've
created it (using CREATE TABLE), loaded it (e.g. using INSERT INTO
VALUES) then used it to operate on other tables (e.g. JOIN). I don't
see how that is equivalent to your description e.g. you cannot JOIN a
base table to a recordset.

For me, the major feature of a temp table is not that it will be
"automatically deleted at the the end of a session/connection", rather
that it is not *available* to any other session/connection (I'd still
explicitly DROP TABLE when I'm done); perhaps the same effect could be
achieved in Access via permissions? I really don't know.

Jamie.

--