You seem to be having trouble imagining good reasons for avoiding
dynamic SQL, so try this. Say you have one Jet mdb and N number of
'front end' applications (Access, VB6, .NET, whatever)
A ridiculous circumstance that hardly ever happens. For that
scenario, it would make *no* sense to put the data in a Jet MDB,
while it would make perfect sense to put the data in SQL Server,
using views and stored procedures in common for all the apps.
In any event, if you're doing that kind of development, it ought to
have an abstraction layer between the front end (in whatever format)
and the back end (in whatever db engine) precisely because that's
one of the best ways to handle multiple front ends to the same data
store.
But that's a very unrealistic *Access/Jet* scenario.
using the
database as a shared application store and each application has
its own 'local' version of some SQL code (i.e. it is held as text
compiled into the application etc) which each return the exact
same resultset. Consider the scenario where the table structure
changes, requiring the SQL code to be changed in order to return
the same resultset. In this scenario you must find the code in N
number of applications and alter it using 1 to N 4GL languages.
Now consider a single application (e.g. Access) that holds the
same SQL multiple times within its VBA code; you have the same
maintenance issue i.e. finding and amending the essentially the
same code multiple times (and what if you miss one...?)
I've never had many problems with this. First off, Speed Ferret does
a darned good job of fixing such issues, but mostly, I don't change
field names after an app has been built, simply because it causes
maintenance issues. So, for all the dynamic SQL I use, it's pretty
easy to avoid issues with this. And there are other ways to fix it,
such as replacing base source tables with stored queries of the same
name that alias the underlying data to be consistent with the code.
That's usually not something I'd do as a long-term solution, but
it's a great way of porting existing code quickly to new
requirements.
Both sceanarios can be ameliorated by storing the SQL code in
a database object as a VIEW (or perhaps a PROCEDURE, although I
recognise that that executing procedures while optionally passing
strongly-typed parameter values is not popular with Access
'purists' because it doesn't fit well with the "always connected
bound controls" approach, and I commonly see names of front end
Forms/controls hard- coded into backend database PROCEDURES, which
strikes me as lacking the necessary level of code separation
between front- and backends). This way, the SQL code can be
considered as part of the schemaas indeed it should, IMO.
You seem to be under the impression that Larry and I are advocating
no stored querydefs, whereas what I've been arguing (I'll let Larry
speak for himself) is that there's little need to edit QueryDefs in
code. That, of course, has exactly the same problems as dynamic SQL
for the scenarios you're talking about, so doesn't really address
*any* of the issues you've raised above.
Likewise, IMO those who fixate on the physical implementation
("file based", "fields and records", etc) are missing the point of
a SQL product (hint: the 'S' used to be for 'Standard').
The S has always stood for "Structured" so far as I'm aware.
No one said that ideas universally applicable or applicable in the
Access world but please do open your mind to the fact that some
SQL concepts and practises may have benefit in the Access world
some of the time.
....And others may be completely irrelevant.
Unless you are querying very large numbers of records, the
performance advantage of saved Queries versus dynamic SQL is not
detectable by the user at the keyboard in the Access environment.
First, more users consume the results of SQL than write SQL
themselves. Second, try thinking in terms of code maintence, code
reuse, coupling and cohesion, etc; Access development is not
totally unrelated to the general principles of good software
engineering. Perhaps, Larry, you always work alone and have no
need to write code that can be maintained by another coder, or
write 'code for coders' e.g. coding business object model using
class modules for other coders to use in their own code, etc (I
think I know what might be coming next: "In my experience people
who work in teams/choose an OOP approach in VBA are [aim low
here]." <g>).
I think you're mistaking our position for one that rejects *all*
saved queries, whereas what we're rejecting is the insistence on
using stored QueryDefs for performance reasons (and thus, the
necessity to edit them in code). The performance reasons don't
justify editing the stored queries in code, in my opinion (and this
appears to me to be what Larry has clearly argued, as well).
This has little or nothing to do with the issues of maintenance,
because I (and, I suspect, Larry) use stored queries for some
purposes and then often use those stored queries as recordsources
for forms and reports, and apply WHERE clauses with the default
openform/report arguments, as well as occasionally altering the SQL
in the open/load events of the objects themselves.
For data manipulation that doesn't involve presentation in a UI, it
is often the case that I use pure dynamic SQL, however, that is not
uncommonly based on saved queries instead of directly on tables.
But I never need to edit those stored queries for the purposes of
changing criteria -- and that's the usual justification for editing
stored QueryDefs (for performance reasons).
