dcsimg
Results 1 to 7 of 7

Thread: [RESOLVED] Execute Multiple SQL Dependent Queries ond After Another

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    410

    Resolved [RESOLVED] Execute Multiple SQL Dependent Queries ond After Another

    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:
    Code:
    SELECT * FROM tblTest;
    qryTestB
    Code:
    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?

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,322

    Re: Execute Multiple SQL Dependent Queries ond After Another

    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?
    Please remember next time...elections matter!

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,452

    Re: Execute Multiple SQL Dependent Queries ond After Another

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    410

    Re: Execute Multiple SQL Dependent Queries ond After Another

    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.
    Last edited by vb6forever; Aug 12th, 2019 at 12:25 PM.

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,452

    Re: Execute Multiple SQL Dependent Queries ond After Another

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    410

    Re: Execute Multiple SQL Dependent Queries ond After Another

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

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    410

    Re: Execute Multiple SQL Dependent Queries ond After Another

    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.

    Code:
    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
    Last edited by vb6forever; Aug 12th, 2019 at 08:41 PM. Reason: minor correction

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width