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

 

[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.

 

[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.

 

[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

 

[John Vinson]

I guess in this case, the "... if
you can calculate it at will..." part isn't true!

Thanks for the clarification - and yes, that's certainly a case where
the rule isn't applicable. If you can't, you can't!

John W. Vinson[MVP]

 

[Pat Hartman\(MVP\)]

Single table select queries are always updateable as long as you have update
authority, use NO aggregate functions, and additionally if the table is
linked, it must have a primary key or unique index.

Multi-table select queries are updatable using pretty much the same rules.
However, the rules apply to BOTH tables so even if you are only trying to
update tblA, you must have update authority to tblB if it is included in the
query. That is why when you join a totals query to a table, you can't
update the table even though logic says you should be able to. Jet
overrules you and refuses and that brings us back to the original issue of
temp tables.

Search Access help for "updateable queries" for more info. I believe that
there is also an article in the MSDN library.

Yes [SQL Server] Views are similar to queries except that views don't
take parameters

Jet VIEWs (SQL language keywords in uppercase, not shouting <g>) do not
take parameters either.

An Access query which uses the PARAMETERS keyword is the equivalent of
a Jet PROCEDURE
(http://office.microsoft.com/en-us/assistance/HP010322191033.aspx).

Put another way, if you use Jet's

CREATE PROCEDURE (<parameter list>) AS

syntax then open the resulting object's SQL pane in Access, you'll see
the above syntax replaced by the construct

I'm not sure that [SQL Server Views] are updatable

Well, not all Jet VIEWs/Access Query's are updatable and the same
applies to SQL Server.

Broadly speaking, if a row in the view can be mapped to a row in a base
table then the VIEW/Query is updatable. Both engines allow VIEWs to be
updateable; the question is, when does each engine give up trying to
determine whether a VIEW is updateable? It is tempting to assume SQL
Server would try harder but that may not be correct e.g. for testing
whether there are cascade cycles in DRI (foreign keys) across multiple
tables, the Jet 4.0 implementation is better than that of SQL Server
2005 because SQL Server gives up earlier.

Regardless which engine wins that particular race, SQL Server come out
top because it supports INSTEAD OF triggers.e. you can make an
otherwise non-updatable VIEW appear updatable by trapping INSERT,
UPDATE and DELETE operations and handling them 'manually' in a trigger.
SQL Server's WITH CHECK OPTION is also useful in that it does the
opposite i.e. restrict some operations on an updateable VIEW without
the overhead (e.g. maintenance) of an INSTEAD OF trigger. Arguably,
without these extensions Access/Jet needs to be smarter at determining
whether a view is updateable.

I really don't know where you are going with this.

Sharing knowledge for the benefit of the reader. I hope that by
proffering information you will do the same e.g. you know that some
Access Queries are updateable but do you know what makes it updateable
or otherwise? an example of a Query that a human could easily map to a
base table but on which the engine chokes? a link to Jet's functional
spec in this area <vbg>? If you (or anyone else) does, they could post
it here.

Jamie.

 

[Pat Hartman\(MVP\)]

Every time you create an object in the database, that object takes up space.
Deleting the object does not free up space. The only way to regain waste
space is to compact the database. The compact process simply copies all
objects from the existing .mdb to a new .mdb. When the copy is done, the
original .mdb is deleted and the copy is renamed.

Usually when people create temp tables, they populate them. So, when they
are deleted, much more waste space is created. You seem to be just creating
the object and deleting it but never populating it. That is why you don't
recover much space when you compact.

If you think you have bloat that hasn't been cleaned up, you can use the
\decompile argument when you open Access. This is an undocumented tool that
removes all p code from the database. It will sometimes clear up a
corruption problem that nothing else seems to fix.