Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nested queries (97/2000)

    I've used Access for a few years. There is a thing which has puzzled me. Could I please run this past some experts and ask for any light on this matter.

    Here is a simplified example:

    Set rs = CurrentDb.OpenRecordSet("SELECT * FROM qrySelection")

    Where qrySelection is a saved query created in the query designer. In the above, we get a recordset if qrySelection does not contain references to another query. If it does we get an error. e.g. If the SQL seen in the query designer for qrySelection was "SELECT * from tblItems;" (where qrySomeItems was a table) we'd get a result. If qrySelection was "SELECT * from qrySomeItems;" (where qrySomeItems was a predefined query) we'd get an error.

    This would seem fair enough... we could conclude that Access doesn't allow us to nest queries and leave it at that. However Access DOES allow us to nest queries when we use the Query Designer. If, for example, we built a query using qrySomeItems as our source, we would see, in the SQL view something like "SELECT * FROM qrySomeItems;", and it would produce a result. It is possible to nest queries like this several levels deep, sometimes the only way to get the result required.

    Does anybody know why this behaviour occurs or better still, know of a way in which we could overcome this limitation and build recordsets in code from queries in which other queries were nested?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Nested queries (97/2000)

    Hi JVBNZ,
    I have just tried this in Access97 and have successfully tested it.
    What error do you get when you try it?
    Pat

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested queries (97/2000)

    Hi Patt:,

    You are right! I have (for the first time) found that it can work in some cases, including for the hypothetical simplified example I gave. It would seem that every time over the years I have tried this I have chosen a query on which it doesn't work.

    Your comments have been helpful to me in forcing me to find out more about this (mis?)behaviour. So far it seems that if the sub-queries are updatable then the recordset can be created. However if the recordset is not updatable then the error message ("Too few parameters. Expected 2") is received.

    I have tried adding some of the constants (e.g. dbInconsistent) to get a result without success so far.

    Just for the record the following subroutine is what I am using to test. Both queries "qryTransactions" and "qryTotPay" are valid and will produce records in query view. However the above-mentioned error is received with qryTotPay.

    I guess I should now rephrase my original question and ask if anybody knows why some queries containing nested queries can be used to create recordsets and why some can't, or if there are any settings which can overcome this behaviour.

    Thanks for your help.

    Jim.

    Sub xxx()
    Dim rs As Recordset

    'Set rs = CurrentDb.OpenRecordset("SELECT * From qryTotPay")
    Set rs = CurrentDb.OpenRecordset("SELECT * From qryTransactions")

    rs.MoveLast
    MsgBox rs.RecordCount
    rs.Close
    Set rs = Nothing
    End Sub

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Nested queries (97/2000)

    Hi Jim,
    The error message "Too few parameters. Expected 2" means you have a couple or the same field that is undefined in the query.

    Post your DB if it is does not contain sensitive data and I'll have a play.
    Pat

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Nested queries (97/2000)

    When the underlying query requires parameters to be passed in, including criteria that reference a form, you may encounter this error when you open a recordset in code as you have here. Running the same query (SELECT * From qryTransactions) as a query should pop up parameter dialogs.
    Charlotte

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Nested queries (97/2000)

    Good call Charlotte !!

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested queries (97/2000)

    Thank you Charlotte... this is the situation here. A query nested below qryTotPay refers to a form which defines a date gate. The query qryTotPay works when run interactively from the Query Designer or when it is used as the basis of a report. However it does not run when used as the basis of a recordset.

    Do you know of any way of correcting this behaviour? If it works for a report it should work for a recordset, I would have thought.

    I can find a workaround in this case by creating another nest of queries which don't refer to a form, but it would be nice to have a way of making this unnecessary. The database frontend already has 198 queries and I'd prefer to work with what what's already there instead of adding another bunch.

    Regards,

    Jim.

  8. #8
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested queries (97/2000)

    Thanks Pat... As you can see Charlotte has identified the culprit in this case. I've run into this problem several times before. The reaons why I had the problem is that right at the bottom of the stack of queries I have a query which dategates the data using a hidden form. So while reports and interactive queries have worked, recordsets haven't. I illogically jumped to the conclusion that queries couldn't be stacked for any recordsets. Your help in setting this straight is much appreciated. Also your offer of having a look at the database - although it was actually too big to travel (a 4 megabyte front-end, to say nothing of the data).

    Regards,

    Jim

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Nested queries (97/2000)

    The way I usually handle it is to create a temporary querydef in code and pass it the parameters it needs. You could do it something like this:

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb()
    Set qdf = dbs.CreateQueryDef("","SELECT * FROM qrySelection")

    qdf.Parameters(0) = Forms!formname!controlname
    qdf.Parameters(1) = ... etc.

    Set rst = qdf.OpenRecordset

    To make this work, you may have to add the parameters to the Parameters collection of the querydef as well as referring to them in the criteria of the query.
    Charlotte

  10. #10
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested queries (97/2000)

    Hi Charlotte,

    You have been extraordinarily helpful. I attach the code which produces the result I wanted based on what you posted. As you can see I have been able to get the SQL from the predefined query using a function from my library so the amount of work required is absolutely minimal.

    The flexibility of the method you have showed me is awesome because it enables the building up of complex queries interactively (and this one is very complex!) together with the ability to access the results in code.

    Thank you again.

    Jim.

    Sub testxyz()
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb()
    Set qdf = dbs.CreateQueryDef("", GetSQLFromQueryDef("qryCheckCashStatementEquation" ))

    qdf.Parameters(0) = Forms!frmUtilityHidden!txt1
    qdf.Parameters(1) = Forms!frmUtilityHidden!txt2

    Set rst = qdf.OpenRecordset
    rst.MoveLast
    MsgBox rst.RecordCount

    rst.Close
    Set rst = Nothing
    End Sub

    Public Function GetSQLFromQueryDef(sQryName As String)
    Dim db As Database, qd As QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(sQryName)
    GetSQLFromQueryDef = qd.SQL
    Set qd = Nothing
    Set db = Nothing
    End Function

Posting Permissions

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