Query calling query - how do you do this in VB 
Author Message
 Query calling query - how do you do this in VB

I'm desperate to find the answer.

We will use DAO or ADO, makes no difference.

We have a large disributed (to hundreds of users) Access app being converted
to VB6 and have some {*filter*} queries that are dependent on other queries.

What is the method of doing this in VB 6.... with the concept that we don't
want to keep the queries in the MDB. Why? Because I don't think there is
anyway I can update those queries (replace them) via update programs we send
to the users from time to time.

Or am I wrong. Is there a way "via Query Defs" or something that will allow
us to write a program that will update the MDBs in the field?

What is the technique the "pros" use when they have queries calling queries,
but want to keep the SQL in the VB front end?

Will the ADO Data Environment help me here?

I have two Database Programming books (WROX, and Jennings) and can't find
the answer anywhere!!! Can anyone point me in the right direction... a web
site... any place???

Thanks,

Paul



Thu, 01 Nov 2001 03:00:00 GMT  
 Query calling query - how do you do this in VB

Quote:

> I'm desperate to find the answer.

> We will use DAO or ADO, makes no difference.

> We have a large disributed (to hundreds of users) Access app being converted
> to VB6 and have some {*filter*} queries that are dependent on other queries.

> What is the method of doing this in VB 6.... with the concept that we don't
> want to keep the queries in the MDB. Why? Because I don't think there is
> anyway I can update those queries (replace them) via update programs we send
> to the users from time to time.

> Or am I wrong. Is there a way "via Query Defs" or something that will allow
> us to write a program that will update the MDBs in the field?

> What is the technique the "pros" use when they have queries calling queries,
> but want to keep the SQL in the VB front end?

> Will the ADO Data Environment help me here?

> I have two Database Programming books (WROX, and Jennings) and can't find
> the answer anywhere!!! Can anyone point me in the right direction... a web
> site... any place???

> Thanks,

> Paul

What's the problem ? You can update the queries as you like, from code,
like this:

Private Sub Command1_Click()

    On Error GoTo Command1_ClickError

    Dim dbWork As Database
    Dim qryQuery As QueryDef
    Dim intOpenedSoFar As Integer

    Set dbWork = OpenDatabase("C:\temp\test1.mdb", False)
    intOpenedSoFar = 1

    'Update query
    Set qryQuery = dbWork.QueryDefs("Query1")
    qryQuery.SQL = "SELECT Table1.C, Table2.E FROM Table1 INNER JOIN
Table2 ON Table1.A = Table2.D;"
    dbWork.QueryDefs.Refresh

    'Update query on query
    Set qryQuery = dbWork.QueryDefs("Query2")
    qryQuery.SQL = "SELECT Query1.C AS Expr1 From Query1 WHERE
((([Query1].[B])='A'));"
    dbWork.QueryDefs.Refresh

    'New query
    dbWork.CreateQueryDef "Yet Another Query", "Select * from Table1
order by A;"

Command1_ClickExit:
    If intOpenedSoFar = 1 Then
        dbWork.Close
        Set dbWork = Nothing
    End If
    Set qryQuery = Nothing
    Exit Sub

Command1_ClickError:
    MsgBox Error
    Resume Command1_ClickExit
End Sub

I suggest you keep your queries in the db for better performance. You
can update your queries with patches like this one.



Sat, 03 Nov 2001 03:00:00 GMT  
 
 [ 2 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. Query calling query - how do you do this in VB

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

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

12. Query call query in VB6

 

 
Powered by phpBB® Forum Software