[AccessD] Weird VSS issue - again. X-posted to Access-L.

Hey Gustav,

At the time of creation/editing/saving all of the field names are known.
These are not cross-tab queries.

The weird thing is that it works ok most of the time.  And when it does mess
up, it messes up for all of the seven queries (that are usually affected)
that have been edited (at least the 7 queries that it now affects).  All of
the other queries are fine.

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, August 23, 2005 2:19 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Weird VSS issue - again. X-posted to Access-L.


Hi Bobby

I don't use VSS, but could it be that some of the field names of the
subqueries are not known at design time?
This may happen if one or more of these are crosstab queries, and it
will cause troubles if you cannot specify (hardcode) the output columns
or have not done so.

/gustav

>>> bheid at appdevgrp.com 08/23 7:47 pm >>>
Hey all,

We have a fairly large FE with hundreds of queries in it (Acc XP). 
There is this one set of queries that use a pretty complex sub-query. 
By complex, I mean that there are a lot of joins and criteria in it.

Whomever did the original design then self-joined the sub-query 5
times. This is to handle the tiering of the data.

Any way, after building from source in VSS, this one query would
sometimes be blank.  We finally tracked down how to prevent it from
happening.  We had to view the query in SQL view and then save it.

Well, we just had to add another tier to some other queries (which are
like the ones above) and the same thing happened upon building from
source.  I believe it has something to do with the complexity of the
queries.

Does anyone know what might be going on and what we can do to stop it
from happening?  It's becoming a real PIA.

Thanks,
Bobby