[RESOLVED] Execute Multiple SQL Dependent Queries ond After Another

[vb6forever]

I believe this is a VB5/6 question NOT an Office or Database question.

I'm using Access and have made some stored queries (see below) for testing.
In Access query Builder, I:
1) Execute (double click) "TestA" and get back a recordset.
2) Execute (double click) "TestB" and get back a recordset.

"TestB" calls "TestA" and is dependent on "TestA" being executed first.

I cannot get "TestB" to be accepted as valid SQL in VB5/6 in order to run the query.

qryTestA:

SELECT * FROM tblTest;

qryTestB

SELECT TestA.fldHistDateTime, CDbl(TestA.fldHistDateTime)
FROM TestA;

------------
Question
------------
Is it possible to construct and execute dependent queries in VB5/6 -- using Access or any other DB for that matter as a backend -- and if so, how?

[TysonLPrice]

I don't know about Access but in MS SQL you can just call the stored procedure from the other stored procedure. Even better can you combine them into just one?

[techgnome]

Queries are just that - queries... they get executed when they are called.If qryA is just a simple select * from some table (which isn't a good idea in the first place) and query b is just using a couple fields form the first one... which is selecting everything from a table... then why have it in the first place? Just select what you want from the table directly.
What is the error you're getting?


-tg

[vb6forever]

Thanks all for responding:
---------------
techgnome
---------------
Some History:
1) I first tried to execute a SQL query which included multiple SubQueries as part of SELECT.
While the SQL executed, it ALWAYS returned bad data in one field (column) of the recordset.

2) I decided if I could break Any query into multiple queries it might work.
I did a quick test (see post #1) in Access Query Builder and was able to
execute TestA and TestB -- manually -- in succession and get a recordset of a recordset.

3) I then tried to use VB5/6 to execute TestA and TestB as stored Access Queries.
Could not figure out how to do this.
If I recall correctly, the recordset from TestA was returned to VB5/6, but the call to TestB then failed.

4) I then tried to pass the SQL strings from VB5/6 as an UNstored Access Query.
Again TestA worked fine. VB5/6 would NOT accept TestA in TestB FROM clause (assume because Not a Table).

So after killing a week trying multiple things, this post.
With my limited knowledge in this area, guessing it has something to do with COM and marshalling objects
across different Apps, but hoping someone might have a solution as I can see where this (query calling a query) could
have many uses.

[techgnome]

Since you aren't sharing what the error is, or any code, I can only assume it's all a big secret.
VB5/6 doesn't care what it is, it's just SQL... so if it's not running from VB you're doing it wrong. But I wouldn't know since you never share the code or the error message.

SQL is SQL ... if you can run it in Access, then generally it's runnable from outside... but it depends on how it was created and how you're trying to run it.

-tg

[vb6forever]

I can only assume it's all a big secret.

No big secret. Got to crawl before walk.
Not focused on "ANY given query" per se at this point even the one that failed with multiple subqueries.
MOST INTERESTED in the process so I can understand what I'm doing wrong.
As Unstored Query (having the SQL in VB5/6), VB errored on the FROM clause -- as I recall error "Not a Table", but let me
go back and get the specific Error # and Description (my error for not providing it in post #1).

[vb6forever]

techgnome:
Put together a little example for you.
It Works. Can't explain it !!!!!!
My apologizes for taking up the forums time.
Here's example - may help someone else.

Private Sub QueryOnQuery_Test()

   #If kDEBUGON Then
       Debug.Print "Begin QueryOnQuery_Test"
   #End If

   On Error GoTo Error_QueryOnQuery_Test
   
   '-----------------
   
   Dim dtmDT As Date
   Dim dblOut As Double
'   Dim strParam As String
   Dim strSQL As String
   Dim qryName As String

   
   'Objects
   Dim qd As QueryDef
   Dim rsQuery As DAO.Recordset

   '*******
   'STARTUP
   '*******
   
   '*****
   'MAIN
   '*****
   '================================================
   ' Execute Previously Saved Access Stored Queries
   'THIS WORKS
GoTo VBCreateStored
   '=================================================
   
   '------
   'Query1
   '------
   qryName = "TestA"
   Set qd = DaoDb.QueryDefs("" & qryName & "")
'   With qd
'      .Parameters("pBegDate").Value = dtmDateBeg
'      .Parameters("pEndDate").Value = dtmLastDate
'   End With

   Set rsQuery = qd.OpenRecordset
   
   dtmDT = rsQuery!fldHistDateTime
     
    '------
   'Query2
   '------
   qryName = "TestB"
   Set qd = DaoDb.QueryDefs("" & qryName & "")
'   With qd
'      .Parameters("pBegDate").Value = dtmDateBeg
'      .Parameters("pEndDate").Value = dtmLastDate
'   End With

   Set rsQuery = qd.OpenRecordset
   
   dblOut = rsQuery!ThisDouble
   
   
VBCreateStored:

   '==============================================
   'Create Access Stored Query From VB and Execute
   'THIS WORKS
GoTo VBCreateUnStored
   '==============================================
   '----------
   'Query1
   '------
   strSQL = "SELECT [fldHistDateTime], CDbl([fldHistDateTime]) As ThisDouble FROM tblTEST;"
   
   Set qd = DaoDb.CreateQueryDef("TestC", strSQL)             '<<Stored

   Set rsQuery = qd.OpenRecordset      '(dbOpenForwardOnly)  '(dbOpenSnapshot)
   
  dtmDT = rsQuery!fldHistDateTime
  dblOut = rsQuery!ThisDouble
  
  
   '----------
   'Query2
   '------
   strSQL = "SELECT [fldHistDateTime], [ThisDouble] FROM TestC;"
   
   Set qd = DaoDb.CreateQueryDef("TestD", strSQL)             '<<Temporary

   Set rsQuery = qd.OpenRecordset
   
  dtmDT = rsQuery!fldHistDateTime
  dblOut = rsQuery!ThisDouble
   
   
   
VBCreateUnStored:

   '=========================================
   ' Execute Access UNStored Queries From VB
   'FAILS On Query2  (As designed)
   '========================================
   
   '----------
   'Query1
   '------
   strSQL = "SELECT [fldHistDateTime], CDbl([fldHistDateTime]) As ThisDouble FROM tblTEST;"
   
   Set qd = DaoDb.CreateQueryDef(vbNullString, strSQL)             '<<Temporary

   Set rsQuery = qd.OpenRecordset
   
   dtmDT = rsQuery!fldHistDateTime
   dblOut = rsQuery!ThisDouble
  
   '-----------------------------------------
   'Query2
   'WILL NOT EXECUTE Query2 as Query1 UnNamed
   '--------------------------------------------
 
   strSQL = "SELECT [fldHistDateTime], [ThisDouble] FROM TestC;"
   
   Set qd = DaoDb.CreateQueryDef(vbNullString, strSQL)             '<<Temporary

   Set rsQuery = qd.OpenRecordset      '(dbOpenForwardOnly)  '(dbOpenSnapshot)
   
  dtmDT = rsQuery!fldHistDateTime
  dblOut = rsQuery!ThisDouble
  


   '///////
   'WRAPUP
   '///////

   qd.Close
   'Set qd = Nothing
   rsQuery.Close
   'Set rsQuery = Nothing

   Exit Sub

Error_QueryOnQuery_Test:

Dim Number As Long
Dim strErr As String

    Number = Err.Number
   strErr = Err.Description