[AccessD] Upsize?

Hi Arthur,

Nice rant...  :-)
 
I agree pretty much with you in principle, however-
We us the 'WHERE AccountNumber = @AccountNumber OR @AccountNumber IS
NULL'
syntax most of the time.  There is 1 particular instance where we don't
though.  Our
data warehouse explorer app main search screen builds the SELECT part
and the WHERE 
part dynamically and passes that to a sproc where it is built up into a
valid SQL statement
and exec'd there.  Performance is great BTW, SQL will reuse the
execution plan unless
the statement is very different to the previous execution (which in my
case is likely).
Because the app UI allows the user to select any of about 80 SELECT
fields and 50 WHERE
conditions, some of which my be multiple selected (In('a','b') etc) I
don't see any
real alternatives...   

Do you?  Anyone else?

BTW maint is a breeze, new fields are added from time to time, we just
add em to the
table, update 2 views that contains select columns + where columns, the
UI is now up to
date and the sproc needs no mods.  

The only drawback I can think of is security, to exec you need select
permissions on
the table.  Not really a problem in my case though.



cheers


Michael M



Subject: Re: [AccessD] Upsize?

<snip>

<rant>
There are a VERY few occasions in which dynamic SQL construction is
required. In my experience, almost all occasions in which it is used are
due to the lack of analysis on the part of the programmer. A stored
procedure or table-UDF will execute much more quickly than its
equivalent dynamically-constructed SQL statement, since there is no way
that such a statement can be optimized. 
I have repeatedly encountered the objection that "I may or may not have
parameters x, y and z, the user may have chosen only one or two". This
objection is fallacious. The way to handle this possibility in a stored
procedure is to compare the parameter to itself, as this snippet
illustrates:
SELECT * FROM myTable WHERE AccountNumber = @AccountNumber OR
@AccountNumber IS NULL You can perform this logic for any set of
parameters that might be passed.
If you don't believe me, try it.
I am converting a colleague's app from MDB to SQL currently, and the MDB
contains about 100 occurrences of this sort of code. A form has several
controls on it, any combination of which might be used to create a
filter.
There are dozens and dozens of lines of code that anticipate that this
one is null and the other two are not, etc. It is all silly, in both
Access and SQL. 
In SQL the simplest thing to do is to pass all the values of all the
relevant controls as parameters to the stored procedure, and let it deal
with nulls as outlined above. Dozens or hundreds of lines of code
disappear, replaced by one call to the stored procedure.
In an MDB you need static functions to achieve the same, but that`s
cool.
Typically, you create a query that selects this and that from somewhere.
You can name it and save it, but the problem is that you cannot
conveniently pass parameters to it. If you could, the code would reduce
to one line approximately; but you cannot, so the most common practice
is to dynamically build an SQL statement and run it instead. Numerous
lines of code construct a statement then passed to DoCmd.RunSQL. This is
nonsense, IMO. 
Let us suppose that a given query requires 3 parameters, any or all of
which might be passed. Let us call them Country, Region and Customer.
Suppose that the front lets the user select any combination of these
controls, such that Country only results in a list of every customer in
that country, and so on.
Create a static function that gets or sets the value for each of these:
call them CurrentCountry, CurrentRegion and CurrentCustomer. Each of
these is written to return a numeric value, either zero or greater than
zero (which presumably references the PK of some other related table).
Then write the query invoking these functions rather than addressing the
particular form of interest. Modify the query slightly so that at the
end of the day it resembles this:
SELECT * FROM somewhere
WHERE
Country = CurrentCountry() OR CurrentCountry() = 0 AND Region =
CurrentRegion() OR CurrentRegion() = 0 AND City = CurrentCity() OR
CurrentCity() = 0
---------------------
I wrote this in Outlook, whose syntax checker does not extend to Access,
but I hope this illustrates my points:
a) There is NO need to reference specific forms to obtain specific
values to plug into queries.
b) By wasting your time constructing dynamic SQL queries, you make your
code tough to decipher and also bill the client for unnecessary hours.
c) By creating the static functions to plug into your query, you enable
said query to be used in numerous situations. You can even run it from
the debug window with no forms open. Just call the static functions in
Set mode then run the query. No need to open the form or report. You
will see the results immediately, and if they are wrong then fix the
query not the form or report.
d) on the chance that you might have to convert an MDB to SQL, queries
written this way are easy to port. Static functions can`t be used in SQL
of course, but the query itself can be changed to refer to arguments in
an equivalent stored proc or table UDF. Estimated conversion time: 1
minute per instance.
And finally, the more you do this, the better you will like it.
</rant>
Sorry for the length of the rant, but once I get started watch out! LOL.
A.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: December 16, 2005 3:47 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Upsize?

Pull ALL of the tables into a single db, set the relationships, upsize,
fix errors, upsize, fix errors, repeat until it goes. 


John W. Colby
www.ColbyConsulting.com 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com