In Access97 we have queries that are dependent (ie. "joined") to earlier
queries.

If you need to do this in a VB6.0, do you create temp tables in a local (non
shared) MDB? If so, isn't the overhead pretty high? or is there a way for
SQL to references fields in another Recordset?

If temp talbles are the way to go, instead of putting them in an Access MDB,
what about saving the recordsets as a file (using ADO, of course).

Everyone says to keep our queries in the shared MDB as they will run faster.
So what is the technique when you need queries that link to earlier queries.

Thanks,

Paul

It's also possible in almost all cases to combine all the "sub" queries into
one.  Hope this helps.

Oscar Bowyer

Dana,
I typically set the query parameters before opening the recordset all in code and avoid the data controls. I don't have lots of experience with data controls, but here something you may want to try. First, open the query, set the parameters, and open the recordset and confirm that you are getting records. Then try setting the data control recordset to the opened recordset. Hope this helps.

Oscar Bowyer

    Oscar - you caught my attention when you mentioned in your reply "you need to set these [parameters] before opening the top query."  I'm having trouble setting parameters for Access queries in the Data Environment; when I have a data control bound to the command referencing the query I haven't been able to figure out a way to set the parameter value for the command; the parameter value is not seen at runtime.  The only thing that works in hard-coding a value for the parameter in the Data Environment at design time.  Any suggestions?

        Paul,
        You can open the top level query from VB even if it is dependant on other
        Access "sub" queries. A couple of things to remember, 1) if any of the sub
        queries has parameters (input values), then you will need to set these
        before opening the top query. 2) Your queries can not call an internal
        function written in the .mdb since these are not visible to VB.
        It's also possible in almost all cases to combine all the "sub" queries into
        one.  Hope this helps.

        Oscar Bowyer

        >Ref: ADO
        >
        >In Access97 we have queries that are dependent (ie. "joined") to earlier
        >queries.
        >
        >If you need to do this in a VB6.0, do you create temp tables in a local
        (non
        >shared) MDB? If so, isn't the overhead pretty high? or is there a way for
        >SQL to references fields in another Recordset?
        >
        >If temp talbles are the way to go, instead of putting them in an Access
        MDB,
        >what about saving the recordsets as a file (using ADO, of course).
        >
        >Everyone says to keep our queries in the shared MDB as they will run
        faster.
        >So what is the technique when you need queries that link to earlier
        queries.
        >
        >Thanks,
        >
        >Paul
        >
        >
        >