Selecting from a Parameterised query in a CreateQuerydef'd query 
Author Message
 Selecting from a Parameterised query in a CreateQuerydef'd query

Hi all

This one's been driving me nuts! Sorry if this post is a bit long, I
want to make sure I don't miss anything :-)

Basic details:
Access 97 backend, VB6 frontend (although the same behaviour is
exhibited in an access module), DAO

I've got a parameterised query in access that I need to apply a where
clause to depending on what options the user selects in the frontend.
If the user doesn't specify any options, I grab the query from
db.Querydefs, set the parameters and open the recordset. If they *do*
specify some options, I construct some SQL that selects from the
parameterised query, create a temporary query using
CreateQuerydef("",SQL), set the parameters and open the recordset.
The first one (grab from Querydefs) works a treat, as you would
expect.
The second one (construct SQL then CreateQuerydef) doesn't work, it
dies in a couple of different ways
* with a 3265 "Item not found in this collection"
  when I try to set the paramter
* if I comment out the parameter setting line, it dies
  with a 3420 "Object invalid or no longer set"
  when I try to open the recordset

here's some code that that'll perhaps explain it better
[obviously nonsense names, and currentdb put in to]
[allow quicker testing in access]
Public Sub TestParamQuery()
    Dim lobjQ As DAO.QueryDef
    Dim lobjDB As DAO.Database
    Dim lstrSQL As String
    Dim lobjRS1 As DAO.Recordset
    Dim lobjRS2 As DAO.Recordset

    Set lobjDB = CurrentDb
    lstrSQL = "SELECT * FROM qryAParamQuery WHERE a_field IN (1,2,3)"
    Set lobjQ = lobjDB.CreateQueryDef(, lstrSQL)
    lobjQ.Parameters("pTheParam").Value = 1 '<< Item not found in this
collection
    Set lobjRS1 = lobjQ.OpenRecordset       '<< Object invalid or no
longer set
    Debug.Print lobjRS1.RecordCount
End Sub

In order to get it working, I ended up grabbing the query from
Querydefs, opening a recordset, filtering that recordset and opening a
new recordset. {*filter*} slow stuff, but it works. See below for the code
Public Sub TestParamQueryAgain()
    Dim lobjQ As DAO.QueryDef
    Dim lobjDB As DAO.Database
    Dim lstrSQL As String
    Dim lobjRS1 As DAO.Recordset
    Dim lobjRS2 As DAO.Recordset

    Set lobjDB = CurrentDb
    Set lobjQ = lobjDB.QueryDefs("qryAParamQuery")
    lobjQ.Parameters("pTheParam").Value = 1
    Set lobjRS1 = lobjQ.OpenRecordset
    lobjRS1.MoveLast
    Debug.Print "LR1: " & lobjRS1.RecordCount
    lobjRS1.Filter = "a_field IN (1,2,3)"
    Set lobjRS2 = lobjRS1.OpenRecordset
    lobjRS2.MoveLast
    Debug.Print "LR2: " & lobjRS2.RecordCount
End Sub

A bit more background information
* The query *has* to be parameterised as it has a crosstab query a bit
further down the chain and I need the correct columns returned based
on one of the paramters.
* I've tried every combination of With in the code that I could think
of, nothing helped.
* I tried .Parameters.Refresh, didn't help.
* I tried putting the PARAMETERS declarations into the paramterised
query (rather than letting JET/DAO determine the parameters), and all
the queries that it calls on, didn't help.
* I even tried putting "WHERE (a_field IN (1,2,3)) AND [pTheParam] =
[pTheParam]" (desperation was kicking in) to try and get the
parameters filled, still no joy.
* I'd really prefer not to create the query as a proper querydef with
a unique temporary name then delete it after it's used as I've had
nothing but problems with this approach in the past.

I was wondering if anyone knew why the original method is failing, and
if there's anything I can do to get around it, apart from the
open-filter-open approach. Its probably no biggie, open-filter-open is
a tad slow but it works. I'm just *really* curious why CreateQuerydef
that references a parameterised query fails.

TIA
Dan



Fri, 04 Nov 2005 10:22:13 GMT  
 Selecting from a Parameterised query in a CreateQuerydef'd query
Got it sorted.

Turns out you HAVE to pass an empty string ("") to the CreateQuerydef
method if you want a temporary querydef, or it'll fail in the exact
manner I described. Leaving the *optional* parameter blank just isn't
good enough...

So, change

    Set lobjQ = lobjDB.CreateQueryDef(, lstrSQL)
to
    Set lobjQ = lobjDB.CreateQueryDef("", lstrSQL)

and its all good

For crying out loud Microsoft, its *optional*. Why make it optional if
you *have* to pass something in? Even a runtime error saying "oi,
gimme a name" would have been preferable to the complete nonsense
errors I was getting.
</rant>

Dan

Quote:

> Hi all

> This one's been driving me nuts! Sorry if this post is a bit long, I
> want to make sure I don't miss anything :-)

> Basic details:
> Access 97 backend, VB6 frontend (although the same behaviour is
> exhibited in an access module), DAO

> I've got a parameterised query in access that I need to apply a where
> clause to depending on what options the user selects in the frontend.
> If the user doesn't specify any options, I grab the query from
> db.Querydefs, set the parameters and open the recordset. If they *do*
> specify some options, I construct some SQL that selects from the
> parameterised query, create a temporary query using
> CreateQuerydef("",SQL), set the parameters and open the recordset.
> The first one (grab from Querydefs) works a treat, as you would
> expect.
> The second one (construct SQL then CreateQuerydef) doesn't work, it
> dies in a couple of different ways
> * with a 3265 "Item not found in this collection"
>   when I try to set the paramter
> * if I comment out the parameter setting line, it dies
>   with a 3420 "Object invalid or no longer set"
>   when I try to open the recordset

> here's some code that that'll perhaps explain it better
> [obviously nonsense names, and currentdb put in to]
> [allow quicker testing in access]
> Public Sub TestParamQuery()
>     Dim lobjQ As DAO.QueryDef
>     Dim lobjDB As DAO.Database
>     Dim lstrSQL As String
>     Dim lobjRS1 As DAO.Recordset
>     Dim lobjRS2 As DAO.Recordset

>     Set lobjDB = CurrentDb
>     lstrSQL = "SELECT * FROM qryAParamQuery WHERE a_field IN (1,2,3)"
>     Set lobjQ = lobjDB.CreateQueryDef(, lstrSQL)
>     lobjQ.Parameters("pTheParam").Value = 1 '<< Item not found in this
> collection
>     Set lobjRS1 = lobjQ.OpenRecordset       '<< Object invalid or no
> longer set
>     Debug.Print lobjRS1.RecordCount
> End Sub

> In order to get it working, I ended up grabbing the query from
> Querydefs, opening a recordset, filtering that recordset and opening a
> new recordset. {*filter*} slow stuff, but it works. See below for the code
> Public Sub TestParamQueryAgain()
>     Dim lobjQ As DAO.QueryDef
>     Dim lobjDB As DAO.Database
>     Dim lstrSQL As String
>     Dim lobjRS1 As DAO.Recordset
>     Dim lobjRS2 As DAO.Recordset

>     Set lobjDB = CurrentDb
>     Set lobjQ = lobjDB.QueryDefs("qryAParamQuery")
>     lobjQ.Parameters("pTheParam").Value = 1
>     Set lobjRS1 = lobjQ.OpenRecordset
>     lobjRS1.MoveLast
>     Debug.Print "LR1: " & lobjRS1.RecordCount
>     lobjRS1.Filter = "a_field IN (1,2,3)"
>     Set lobjRS2 = lobjRS1.OpenRecordset
>     lobjRS2.MoveLast
>     Debug.Print "LR2: " & lobjRS2.RecordCount
> End Sub

> A bit more background information
> * The query *has* to be parameterised as it has a crosstab query a bit
> further down the chain and I need the correct columns returned based
> on one of the paramters.
> * I've tried every combination of With in the code that I could think
> of, nothing helped.
> * I tried .Parameters.Refresh, didn't help.
> * I tried putting the PARAMETERS declarations into the paramterised
> query (rather than letting JET/DAO determine the parameters), and all
> the queries that it calls on, didn't help.
> * I even tried putting "WHERE (a_field IN (1,2,3)) AND [pTheParam] =
> [pTheParam]" (desperation was kicking in) to try and get the
> parameters filled, still no joy.
> * I'd really prefer not to create the query as a proper querydef with
> a unique temporary name then delete it after it's used as I've had
> nothing but problems with this approach in the past.

> I was wondering if anyone knew why the original method is failing, and
> if there's anything I can do to get around it, apart from the
> open-filter-open approach. Its probably no biggie, open-filter-open is
> a tad slow but it works. I'm just *really* curious why CreateQuerydef
> that references a parameterised query fails.

> TIA
> Dan



Tue, 08 Nov 2005 15:19:36 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Parameterised queries in ADO

2. Updateable recordsets from parameterised queries in ADO

3. ADO Parameterised queries

4. ADO : Parameterised Queries

5. Parameterised queries need help

6. Parameterised query problem

7. ADO : Parameterised queries

8. Parameterised queries in data env

9. Problem built in parameterised Access queries and dates

10. DAO, ODBC, CreateQueryDef, Pass Through Query, Form Population Problem

11. Getting a resultset from a SELECT query embedded in another query

12. SELECT queries - Use Access query or not?

 

 
Powered by phpBB® Forum Software