[AccessD] Query SQL correct but not visible in design view

Trust me on this, JWC. Don't use SELECT * ever. Really ugly things can 
happen when dealing with MS-SQL -- not in any simple query but insert a 
column in some table referenced by views or sprocs that say SELECT * and 
let's go get drunk. I'm guessing, but I think that the underlying reason 
is that the query optimizer/compiler refers to columns numerically not 
by name, and if you suddenly change a table or a view that uses SELECT * 
then all hell can break loose. I haven't exhaustively tested this thesis 
to see what breaks under what conditions, but I have been bitten enough 
times to stay away from the SELECT * dog.

Next and perhaps more important, why are you building a SQL statement on 
the fly? This is a bad habit inherited from Access MDB development, I 
think. True, there are times when no other approach is possible, but I 
think that those times amount to maybe 10% at most of the typical app's 
requirements. I suggest that you re-think this part of the app and think 
about feeding the parms to one or more sprocs instead of constructing a 
dynamic query.

A tip from one of my SQL Tips columns.... Allow nulls to be passed to 
the sproc, then CASE the parms and then branch to the appropriate sproc. 
That way each sproc is optimized for the parms and won't screw up with 
choosing the wrong plan. If you write a sproc that involves different 
order by's etc. all in the same sproc, you can get ghastly performance 
because SQL will use the existing plan unless you specifically tell it 
not to.

Arthur

John W. Colby wrote:

>I am building a SQL statement "on the fly" which essentially compares
>matching fields in two different tables - Tbl1.Lname <> Tbl2.Lname.  The SQL
>runs but only the tables and their joins are visible in the QBE grid.  There
>are no fields visible etc.  Does anyone understand what causes Access to do
>this?  I have had the same thing occur even with something as simple as a
>SELECT * from tbl1 inserted into the SQL view.  Tbl1 is displayed, but
>nothing in the grid.  The query executes correctly.
>
>Anyone?
>
>John W. Colby
>www.ColbyConsulting.com 
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.298 / Virus Database: 265.6.4 - Release Date: 12/22/2004