Create ADO recordset w/param stored qry

[Magster]

Hello,

I've searched quite a bit on this subject, and I never find an answer so I always revert back to keying in the sql statement into VBA and creating my ADO recordset. But, what I'd like to do is run the stored param query (it which works fine - it has one param and I entered the form's field name in the column and also on the Param window for the query.)

So, I start with the basics of the connection and recordset, I have a select parameter query called Query1. How do I open the recordset using my query?

dim cnn as ADODB.connection
dim rst as ADODB.recordset

set cnn = new ADODB.Connection
set cnn = currentproject.connection

set rst = new ADODB.Recordset

' need to open the recordset here...


thanks
Magster

 

[nIGHTmAYOR]

all you miss is just a line saying :

rst.Open "Query1", cnn

and yes , access programmers just copy paste DAO scripts so you bring ADO to a discussion and too much hair scratching arise
(Commenting his 6 hours wait )

 

[Magster]

Thank you for your reply - and yes, that works fine if the query isn't pulling values from the open form, but when you have criteria in the query that pulls the values from the open form, the open statement errors out.

 

[jal]

Thank you for your reply - and yes, that works fine if the query isn't pulling values from the open form, but when you have criteria in the query that pulls the values from the open form, the open statement errors out.

I'm a beginner, but I'll take a guess at your problem, and my answer will be sketchy (sorry). Try using the queryDef object - you can access your query and and its parameters. For example:


Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("fillGrid", "Select * From Posted")

You can use the CurrentDB.QueryDefs collection to access your query. And then use the querryDef.Parameters collection to set the values.

I've never tried it, this is just a wild guess based one some scattered code snippets i found in my notebook.

 

[nIGHTmAYOR]

please include me an abstracted sample and i'll see what i can do

 

[LPurvis]

>> "and yes , access programmers just copy paste DAO scripts so you bring ADO to a discussion and too much hair scratching arise "


The problem is an old one. And it affects similarly both DAO and ADO. It's just a question of evaulating the form references which neither DAO nor ADO is capable of doing - as the Access expression service isn't available to them directly (hence we employ a function call - which is available, beyond the Jet VBA library, while we're using an Access object as the parent - e.g. CurrentDb or CurrentProject).
It's just a case of bringing in a command object.

    Dim cmd As New ADODB.Command
    Dim prm As ADODB.Parameter
    Dim rst As New ADODB.Recordset
 
    Set cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "SELECT * FROM qryParametersQuery"
    cmd.Parameters.Refresh 'Optional here, but makes your intention clear
    For Each prm In cmd.Parameters
        prm.Value = Eval(prm.Name)
    Next
 
    rst.Open cmd, , adOpenKeyset

If you wanted to be exact - you could assign the references by the ordinal position in the parameters collection or by name of the Form expression.
The analogy in DAO is like this standard, complete function

Function fDAOGenericRst(strSQL As String, Optional intType As DAO.RecordsetTypeEnum = dbOpenDynaset, _
                                          Optional intOptions As DAO.RecordsetOptionEnum, _
                                          Optional intLock As DAO.LockTypeEnum, _
                                          Optional pdb As DAO.Database) As DAO.Recordset
    Dim db As Database
    Dim qdf As QueryDef
    Dim rst As DAO.Recordset
    Dim prm As DAO.Parameter
 
    If Not pdb Is Nothing Then
        Set db = pdb
    Else
        Set db = CurrentDb
    End If
 
    On Error Resume Next
    Set qdf = db.QueryDefs(strSQL)
    If Err = 3265 Then
        Set qdf = db.CreateQueryDef("", strSQL)
    End If
    On Error GoTo 0
 
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next
 
    If intOptions = 0 And intLock = 0 Then
        Set rst = qdf.OpenRecordset(intType)
    ElseIf intOptions > 0 And intLock = 0 Then
        Set rst = qdf.OpenRecordset(intType, intOptions)
    ElseIf intOptions = 0 And intLock > 0 Then
        Set rst = qdf.OpenRecordset(intType, intLock)
    ElseIf intOptions > 0 And intLock > 0 Then
        Set rst = qdf.OpenRecordset(intType, intOptions, intLock)
    End If
    Set fDAOGenericRst = rst
 
    Set prm = Nothing
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
 
End Function

which, funnily enough, despite being DAO I didn't just copy and paste from somewhere.

 

[datAdrenaline]

As always Leigh's code is impeccable .... so once again, my contribution is merely to add to and give an alternative approach.

To avoid the need to evaluate the form based parameters via code, you can wrap your form parameter with the Eval() function inside your QueryDef. For Example:

SELECT * FROM MyTable WHERE SomeField = Eval("[Forms]![MyForm]![MyControl]")

The query will still run if opened directly in the Access UI.

 

[LPurvis]

I guess it's something to do with the purist in me (yeah right - it's well hidden at times ;-) that steers away from the Eval function call in the query (although I certainly acknowledge it can make an effective answer in forums - quick and easy to implement).

Sometimes such a call is necessary when your local application has trouble with reading the query directly with a form expression (for example when the Expression service is getting no help from anunbound control as to the datatype is should plump for) - we end up wrapping it in a function call anyway.

However I like to think of the queries we write in Access as Jet queries as much as possible. So that, should the need arise - the same query statement is every bit as trivial to execute from an external application source.
In which case the form reference is just a parameter - waiting to be evaluated.
Heh - you could even have a similarly named form and control in the external application which is supply the parameter values! ;-)

All options though.

Cheers.

 

[datAdrenaline]

>> I guess it's something to do with the purist in me (yeah right - it's well hidden at times ;-) that steers away from the Eval function call in the query <<

In my practice of days gone by, I have only used this technique with the source of MSChart Objects within reports. If its not the Eval(), I use wrapper type UDF's. But now I modify the SQL property of the source query.

But it is a good tip to know when your in a pinch ... or when an seeker of information is in a phase of learning that is not receptive to code.

>> quick and easy <<

Yep ...... I have 7 kids to prove I can be that way at times! ...

>> However I like to think of the queries we write in Access as Jet queries as much as possible. So that, should the need arise - the same query statement is every bit as trivial to execute from an external application source.<<

I 100% agree! And, athough I do not typically mess with the parameters collection, I will often resolve the parameters via code and set the SQL property of the QueryDef in question to a literal string (as I mentioned above).

 

[Magster]

Thanks all - I learned some new things from your conversations.

 

[datAdrenaline]

Hello Magster ...

Your welcome! ... were always glad to help out!