CREATE TEMPORARY TABLE

[David Rigler]

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

 

[Duane Hookom]

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

 

[Tim Ferguson]

. 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

 

[Pat Hartman\(MVP\)]

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]

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

 

[David Rigler]

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.

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

 

[Jamie Collins]

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.

--

 

[Tim Ferguson]

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

 

[Pat Hartman\(MVP\)]

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.

 

[Jamie Collins]

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/assistance/HP010322231033.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.

--

 

[jacksonmacd]

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.

I've simulated that behaviour in a split FE/BE Access environment by
creating a third MDB file on the local workstation, and creating the
temporary tables within that database. Since the MDB file is local to
the user's machine, it is unavailable to any other users.

I think that trying to accomplish the same result with permissions
would be difficult because all users would see the same-named table,
and you would need to populate a user-specific field within the temp
table in order to differentiate between users. Creating a new MDB file
on their computer automatically differentiates between users.
**********************[email protected]
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Pat Hartman\(MVP\)]

In an Access query, you can join queries to queries or queries to tables.
If the queries are updatable, the join to the table will most likely be
also.

In SQL Server, you create temp tables because you want/need to do the
processing in steps. Similar functionality can be accomplished in Access by
nesting queries.

 

[Jamie Collins]

In an Access query, you can join queries to queries or queries to tables.
If the queries are updatable, the join to the table will most likely be
also.

In SQL Server, you create temp tables because you want/need to do the
processing in steps. Similar functionality can be accomplished in Access by
nesting queries.

As I said, the closest equivalent to an Access query in SQL Server is a
VIEW. The only thing I can think that you can do in an Access query
that you can't do in a SQL Server VIEW is use an ORDER BY clause. Is
that what you mean? (I don't see how this unrelational feature of
Access would be useful, though.)

Otherwise, can you please post an example of something that cannot be
achieved with a SQL Server VIEW, hence necessitates a temp table, yet
can be achieved using queries in Access.

TIA,
Jamie.

--

 

[Jamie Collins]

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

Actually, I think the OP has a point here. I don't agree with the
suggestions that this syntax relates to SQL Server or an ADP.

Take another look at the help:

http://office.microsoft.com/en-us/assistance/HP010322201033.aspx

CREATE TABLE Statement Help
Assistance > Access 2003 > Creating and Working with Databases and
Objects > Database Objects > Queries > Microsoft Jet SQL Reference >
Data Definition Language

Creates a new table.

Note The Microsoft Jet database engine does not support the use of
CREATE TABLE, or any of the DDL statements, with non-Microsoft Jet
database engine databases. Use the DAO Create methods instead.

Syntax
CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH
COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL]
[index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

When a TEMPORARY table is created it is visible only within the session
in which it was created. It is automatically deleted when the session
is terminated. Temporary tables can be accessed by more than one user.

[unquote]

1) It appears in the Microsoft Jet SQL Reference section of the help;
Access Projects has its own section.

2) It explicitly states Jet does not support the use of CREATE TABLE
for non-Jet engine databases.

3) The syntax includes WITH COMPRESSION which only applies to Jet
engine databases. What would be the point of mixing Jet and non-Jet
syntax?

4) SQL Server's CREATE TABLE syntax does not include the TEMPORARY
keyword; rather, whether a SQL Server table will be created as
temporary is dependent on its own table naming convention:

CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> ...

Prefix local temporary table names with single number sign
(#table_name), and prefix global temporary table names with a double
number sign (##table_name).

[unquote]

I think this is a good spot by the OP. No smoke without fire, so what's
going on here? My guess is there is some unexposed functionality in the
Jet engine that a diligent but uninformed technical author erroneously
included in the help.

Could one of the MVPs (who don't have me on killfile <g>) please ask
questions e.g. in the private Microsoft newgroups? Perhaps someone in
the Access team could be leant on to take a look in their 'private
copy' of the Jet source code?

Jamie.

--

 

[Jamie Collins]

In an Access query, you can join queries to queries or queries to tables.
If the queries are updatable, the join to the table will most likely be
also.

In SQL Server, you create temp tables because you want/need to do the
processing in steps. Similar functionality can be accomplished in Access by
nesting queries.

The only thing I can think [snip]

I've had a further though: perhaps you meant 'materializing' data in
this way:

Sub FakeTempTable()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create dummy table for bulk inserts
.Execute _
"CREATE TABLE DropMe (anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"

Dim rs
' Create query equivalent of a temp table
Set rs = .Execute( _
"SELECT QTags.ESCI, QTags.attribseq FROM (" & _
" SELECT '001234' AS ESCI," & _
" 1 AS attribseq FROM DropMe UNION ALL" & _
" SELECT '001234', 2 FROM DropMe UNION ALL" & _
" SELECT '001234', NULL FROM DropMe UNION ALL" & _
" SELECT '005349', 1 FROM DropMe UNION ALL" & _
" SELECT '005349', 2 FROM DropMe UNION ALL" & _
" SELECT '005349', NULL FROM DropMe UNION ALL" & _
" SELECT '006789', 1 FROM DropMe UNION ALL" & _
" SELECT '006789', 2 FROM DropMe UNION ALL" & _
" SELECT '006789', NULL FROM DropMe)" & _
" AS QTags;")
MsgBox rs.GetString(2, , , , "(null)")
End With
Set .ActiveConnection = Nothing
End With
End Sub

Is this what you meant?

Jamie.

--

 

[Pat Hartman\(MVP\)]

I really don't know where you are going with this. All I said was that when
working with Access, you rarely need to use a temporary table. In most
cases, you can use a query instead. Yes Views are similar to queries except
that views don't take parameters and I'm not sure that they are updatable.
Queries can take parameters and they are updatable in most cases. I find
that most people create temporary tables (which cause database bloat)
because they don't understand that they don't need to. When creating a
stored procedure, temp tables are used because queries can't be nested as
they can with Access so the methods used in the two environments are
different.

In an Access query, you can join queries to queries or queries to
tables.
If the queries are updatable, the join to the table will most likely be
also.

In SQL Server, you create temp tables because you want/need to do the
processing in steps. Similar functionality can be accomplished in
Access by
nesting queries.

The only thing I can think [snip]

I've had a further though: perhaps you meant 'materializing' data in
this way:

Sub FakeTempTable()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create dummy table for bulk inserts
.Execute _
"CREATE TABLE DropMe (anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"

Dim rs
' Create query equivalent of a temp table
Set rs = .Execute( _
"SELECT QTags.ESCI, QTags.attribseq FROM (" & _
" SELECT '001234' AS ESCI," & _
" 1 AS attribseq FROM DropMe UNION ALL" & _
" SELECT '001234', 2 FROM DropMe UNION ALL" & _
" SELECT '001234', NULL FROM DropMe UNION ALL" & _
" SELECT '005349', 1 FROM DropMe UNION ALL" & _
" SELECT '005349', 2 FROM DropMe UNION ALL" & _
" SELECT '005349', NULL FROM DropMe UNION ALL" & _
" SELECT '006789', 1 FROM DropMe UNION ALL" & _
" SELECT '006789', 2 FROM DropMe UNION ALL" & _
" SELECT '006789', NULL FROM DropMe)" & _
" AS QTags;")
MsgBox rs.GetString(2, , , , "(null)")
End With
Set .ActiveConnection = Nothing
End With
End Sub

Is this what you meant?

Jamie.

 

[jacksonmacd]

Pat

In principle, I agree with you. There's one circumstance that I've
found temp tables to be necessary, but perhaps I just don't know the
right way to go about it. Suppose the result of a query is
non-updateable for whatever reason. If I want to use that query's
output to update some other table, then I am out of luck. The only way
that I've found to solve the problem is to create a temp table from
the original query, then to use *that* table to update the table that
I wanted updated in the first place.

Is there a "generic" way to solve this problem without involving the
temp table?

I really don't know where you are going with this. All I said was that when
working with Access, you rarely need to use a temporary table. In most
cases, you can use a query instead. Yes Views are similar to queries except
that views don't take parameters and I'm not sure that they are updatable.
Queries can take parameters and they are updatable in most cases. I find
that most people create temporary tables (which cause database bloat)
because they don't understand that they don't need to. When creating a
stored procedure, temp tables are used because queries can't be nested as
they can with Access so the methods used in the two environments are
different.

Pat Hartman(MVP) wrote:
In an Access query, you can join queries to queries or queries to
tables.
If the queries are updatable, the join to the table will most likely be
also.

In SQL Server, you create temp tables because you want/need to do the
processing in steps. Similar functionality can be accomplished in
Access by
nesting queries.

The only thing I can think [snip]

I've had a further though: perhaps you meant 'materializing' data in
this way:

Sub FakeTempTable()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create dummy table for bulk inserts
.Execute _
"CREATE TABLE DropMe (anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"

Dim rs
' Create query equivalent of a temp table
Set rs = .Execute( _
"SELECT QTags.ESCI, QTags.attribseq FROM (" & _
" SELECT '001234' AS ESCI," & _
" 1 AS attribseq FROM DropMe UNION ALL" & _
" SELECT '001234', 2 FROM DropMe UNION ALL" & _
" SELECT '001234', NULL FROM DropMe UNION ALL" & _
" SELECT '005349', 1 FROM DropMe UNION ALL" & _
" SELECT '005349', 2 FROM DropMe UNION ALL" & _
" SELECT '005349', NULL FROM DropMe UNION ALL" & _
" SELECT '006789', 1 FROM DropMe UNION ALL" & _
" SELECT '006789', 2 FROM DropMe UNION ALL" & _
" SELECT '006789', NULL FROM DropMe)" & _
" AS QTags;")
MsgBox rs.GetString(2, , , , "(null)")
End With
Set .ActiveConnection = Nothing
End With
End Sub

Is this what you meant?

Jamie.

**********************[email protected]
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Pat Hartman\(MVP\)]

Yes, that is one use of temp tables. Again, most people run into this
because they are attempting to store aggregated data which should be
calculated as needed rather than stored.

The "generic" way to solve the problem of having aggregated data that you
want to use to update a table is exactly what you are already doing. Store
the aggregated data in a temp table. Then use the temp table to update the
permanent table.

Pat

In principle, I agree with you. There's one circumstance that I've
found temp tables to be necessary, but perhaps I just don't know the
right way to go about it. Suppose the result of a query is
non-updateable for whatever reason. If I want to use that query's
output to update some other table, then I am out of luck. The only way
that I've found to solve the problem is to create a temp table from
the original query, then to use *that* table to update the table that
I wanted updated in the first place.

Is there a "generic" way to solve this problem without involving the
temp table?

I really don't know where you are going with this. All I said was that
when
working with Access, you rarely need to use a temporary table. In most
cases, you can use a query instead. Yes Views are similar to queries
except
that views don't take parameters and I'm not sure that they are updatable.
Queries can take parameters and they are updatable in most cases. I find
that most people create temporary tables (which cause database bloat)
because they don't understand that they don't need to. When creating a
stored procedure, temp tables are used because queries can't be nested as
they can with Access so the methods used in the two environments are
different.

Jamie Collins wrote:
Pat Hartman(MVP) wrote:
In an Access query, you can join queries to queries or queries to
tables.
If the queries are updatable, the join to the table will most likely
be
also.

In SQL Server, you create temp tables because you want/need to do the
processing in steps. Similar functionality can be accomplished in
Access by
nesting queries.

The only thing I can think [snip]

I've had a further though: perhaps you meant 'materializing' data in
this way:

Sub FakeTempTable()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create dummy table for bulk inserts
.Execute _
"CREATE TABLE DropMe (anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"

Dim rs
' Create query equivalent of a temp table
Set rs = .Execute( _
"SELECT QTags.ESCI, QTags.attribseq FROM (" & _
" SELECT '001234' AS ESCI," & _
" 1 AS attribseq FROM DropMe UNION ALL" & _
" SELECT '001234', 2 FROM DropMe UNION ALL" & _
" SELECT '001234', NULL FROM DropMe UNION ALL" & _
" SELECT '005349', 1 FROM DropMe UNION ALL" & _
" SELECT '005349', 2 FROM DropMe UNION ALL" & _
" SELECT '005349', NULL FROM DropMe UNION ALL" & _
" SELECT '006789', 1 FROM DropMe UNION ALL" & _
" SELECT '006789', 2 FROM DropMe UNION ALL" & _
" SELECT '006789', NULL FROM DropMe)" & _
" AS QTags;")
MsgBox rs.GetString(2, , , , "(null)")
End With
Set .ActiveConnection = Nothing
End With
End Sub

Is this what you meant?

Jamie.

**********************[email protected]
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[John Vinson]

If I want to use that query's
output to update some other table

Well... this operation should VERY rarely be necessary.

If you can calculate it at will (in the query's output), *WHY* store
it?


John W. Vinson[MVP]

 

[jacksonmacd]

Well... this operation should VERY rarely be necessary.

If you can calculate it at will (in the query's output), *WHY* store
it?

In the most recent case that I'm dealing with, the table to be updated
is a linked DBF file that is hardwired to a shapefile to be displayed
in a desktop mapping system. The mapping system has *no* ability to do
on-the-fly calculations, thus, pre-calculation and storage is
required.

I agree with the general rule for not storing intermediate results,
but there will always be exceptions. I guess in this case, the "... if
you can calculate it at will..." part isn't true!

John W. Vinson[MVP]

**********************[email protected]
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security