William Hindman
wdhindman at bellsouth.net
Mon Dec 19 08:14:49 CST 2005
...ok ...I'm doubling the Tylenol ...you're making my neck ache with all
these twists and turns :(
William
----- Original Message -----
From: "John Colby" <jwcolby at ColbyConsulting.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Sunday, December 18, 2005 7:37 PM
Subject: Re: [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.
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>