CREATE TEMPORARY TABLE

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

 

[Jamie Collins]

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.

--

 

[Jamie Collins]

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

Store it when the cost of the calculation is higher than the cost of a
simple read. See:

http://www.dbazine.com/ofinterest/oi-articles/celko4/

Jamie.

--

 

[Jamie Collins]

temporary tables ... cause database bloat

Pat, are you able to address my question (up thread and reposted here)
about bloat, please?

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

Alternatively, perhaps the bloat problem is external to the engine e.g.
there is something in the Access UI or DAO that causes bloat with Jet
SQL DDL avoids?

Any ideas?

Thanks,
Jamie.

--

 

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