[AccessD] Upsize?

What I don't understand Arthur is why you insist on using dedicated static
functions?  You write a static function CurrentCountry(), CurrentRegion(),
CurrentCity(), CurrentThis(), CurrentThat(), CurrentSomethingElse().  Every
time you need another CurrentSomething() you have to stop to construct such
a new function, you have potentially dozens (hundreds?  Thousands?
Millions?) of such functions.

I have ONE static function which I happen to call Fltr() but which you could
call Current() if that made your day.  Observe that the function has a
static COLLECTION which can hold one or up to 32K values.  So my one
function has replaced ALL of your CurrentXXX.  The only place where this
might not be appropriate is if you need to run it a million times since my
function will be about 3 times slower than yours, however where clauses
evaluate the function one time and that is that.  Additionally I don't have
to stop and write a new function (wasting my client's time).  ;-)

'
'Fltr takes two parameters, the filter name and the filter value.
'
'The first syntax can be used to set the filter value:
'
'fltr "MyFltr1", MyFltrValue
'
'The filter lstrName is used as the key into the collection, i.e. when
lvarValue
'is stored, it is stored with a key of lstrName.
'
'The second syntax can be used to retrieve the value of the filter:
'
'fltr("MyFltr1")
'
'The fact that the second parameter is Optional allows us to check whether a
value
'has been passed in.  If no value is passed in, then the assumption is that
the filter
'is expecting to return a value.
'
'Because the filter uses a collection internally to save the values, this
single
'function can store up to 32K different filter values.
'
'Because lvarValue is a variant, the value stored can be pretty much
anything.
'In fact it is necessary to use ctl.VALUE if you want to store an unchanging
value
'from a control, since passing in a pointer to a control will then return
the value
'of the control, which may change over time.
'
Public Function Fltr(lstrName As String, Optional lvarValue As Variant) As
Variant
On Error GoTo Err_Fltr
Static mcolFilter As Collection
Static blnFltrInitialized As Boolean

    If Not blnFltrInitialized Then
        Set mcolFilter = New Collection
        blnFltrInitialized = True
    End If
    If IsMissing(lvarValue) Then
        On Error Resume Next
        Fltr = mcolFilter(lstrName)
        If Err <> 0 Then
            Fltr = Null
        End If
    Else
        On Error Resume Next
        mcolFilter.Remove lstrName
        mcolFilter.Add lvarValue, lstrName
        Fltr = lvarValue
    End If
Exit_Fltr:
Exit Function
Err_Fltr:
        fwErr , , "Error in Function basFltrFunctions.Fltr"
        Resume Exit_Fltr
    Resume 0    '.FOR TROUBLESHOOTING
End Function


John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Sunday, December 18, 2005 2:01 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Upsize?

As usual, JC is right. I would the following: search your front end for
record and row sources that begin with the word "SELECT" and convert all
instances to named queries before doing the upsize. Then these will convert
as well, without the converter giving them incomprehensible names.
You may also have code that dynamically constructs SQL statements using the
values of form controls. This is harder to fix, but in general (depending on
your convention) the string in question is called strSQL or maybe sSQL or
whatever. The way to locate this is to look for the command below the
statement's construction: DoCmd.RunSQL <strSQL>. If you are serious about
doing the conversion, then you want to isolate these instances, consider
whether they can be converted to stored procedures -- and if so, convert
them, and if not, tough luck; identify them as potential bottlenecks and
proceed.
<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.