Queries dependendt on queries. How do you do this in VB? 
Author Message
 Queries dependendt on queries. How do you do this in VB?

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



Sun, 04 Nov 2001 03:00:00 GMT  
 Queries dependendt on queries. How do you do this in VB?
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

Quote:

>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



Sun, 04 Nov 2001 03:00:00 GMT  
 Queries dependendt on queries. How do you do this in VB?

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



Mon, 05 Nov 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Doing a Query in VB.NET

2. Please tell me what I am doing wrong - DAO parameter Append Query :(

3. To the person doing the Skills Search query

4. How to knowing when a query is done

5. Doing a query on some portion of a text field

6. Doing Update/Delete Queries

7. HELP - Doing a Union Query in VB3.0

8. UserConnection and action queries-can it be done?

9. how can I build 2 queries in 1 query(VB SQL query)

10. ADO thinks DBMS is done, but DBMS ain't done

11. Query calling query - how do you do this in VB

12. Query calling query - how do you do this in VB

 

 
Powered by phpBB® Forum Software