Steve Jorgensen wrote:
The reason pass-through queries are a problem for Access (and stored
procedures can only be called via pass-through queries) is that Access cannot
implicitly pass arguments to them, nor edit the records they return. This
means that for a single case, instead of just writing one parameter query, you
may have to create a saved pass-through query, and rewrite its definition from
code before each time you use it
These are certainly limitations, but they are not, for me, anyway, a
problem with Access. With respect to stored procedures, in Oracle
PL/SQL, arguments are passed via the SQL statements. In both Jet and
Oracle back ends, I always write my SQL dynamically anyway (see my last
paragraph here) and contrary to what I think you implied in your initial
response to Hank, I don't think writing Jet SQL (for Jet BE or c/s
linked table approaches) nor PTQ SQL in VBA makes an app more difficult
to manage or increses development time significantly.
Apart from the above disagreement, though, I think your initial advice
to Hank is spot on and as Rick Brandt once scolded me 8) (and you may
have joined him) if you have a well tuned and appropriately developed
Access application as you're encouraging Hank to make sure he has, the
linked table approach should be just as valid method of achieving the
client server application as a PTQ approach. And certainly much easier
with respect from porting the app from a Jet back end to a different
back end.
The problem can be, however, that what works for Jet may not be
optimized in the c/s back end. Though I cannot speak for Postgres, the
issue of something working well in Jet, but not in a c/s environment has
often been my experience with Oracle back ends. Indeed, with very
complex select statements (for example, multiple inline subqueries in
the from clause along with multiple subqueries in the select clause and
corelated and non-corelated where subqueries), I've found it's usually
very much better and faster to perform the selects in the native server
SQL dialect, in my case, Oracle. As well, especially if the developer
is developing an app in support of an existing and well established c/s
(Oracle or other) app and modification of the server tables is not an
option *AND* the existing app may not be particularly well indexed,
linked tables can drop whole columns in certain Jet select queries (I
believe Larry Linson mentions this issue in one of his presentations on
his tripod site, though I think that may have been with MS SQL - it's
been a while since I've looked at his site, so my apologues if I'm
talking out my butt! 8) ).
There may well be similar issues with Postgres, but Hank should be aware
I've not worked with Postgres.
One very positive thing with using PTQ methods is that one needn't worry
about security in the same way one would if linked tables were present.
With respect to making the program more difficult to develop and
maintain, I think that depends on the developer's approach. In both Jet
and Oracle I tend to write VBA to create dynamic SQL, so there's really
not much of a difference. Include the required ODBC connect strings as
constants, so they are written once in a standard module, make sure
connect strings that allow anything other than read only access are not
saved in querydefs then save as an mde and security is not a worry when
you don't have linked tabledefs saved anywhwere.
Additionally, I find writing joins in Oracle's theta join style versus
the Jet Ansi style infinitely easier to do. Unfortunatey, for Hank,
this is probably irelevant as I seem to recall that Postgres uses ansi
joins (my reference for this is the very good overview book, _SQL in a
Nutshell_ by Kline). It really pisses me off that the folks involved
with the various ANS/SQL standards (89, 92 and 99 - is there anything
later?) recommend the ansi joins. Consider the following example (with
no criteria other than join specification) which has an inner and an
outer join. I just find this so, so much easier to write intuitively in
Oracle. Especially so if my SQL writing function in VBA will need to
include a variable number of tables based on a user's choices!
ANSI Join Method (Jet)
SELECT
<snip>
FROM
(tbl1 inner join tbl2 on tbl1.1_pk = tbl2.2_1_fk)
left join tbl3 on tbl1.1_pk = tbl3.3_1_fk
THETA Join Method (Oracle)
SELECT
<snip>
FROM
tbl1, tbl2, tbl3
WHERE
tbl1.1_pk = tbl2.2_1_fk and
tbl1.1_pk = tbl3.3_1_fk (+)
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto