[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.
-----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