Getting the text of a query

Getting the text of a query

Post by Q2hhaW » Fri, 11 Mar 2005 08:03:04

In an Access 2000 app, I have a global adodb.connection and recordset. What I
would like to do is have a sub that takes the text of a query (in the Queries
collection) as a string and opens the recordset based on that query. My code
looks like:

private sub establishRecordset (qryStr as string)
set rst = new adodb.recordset qryStr, conn ' the recordset and connection objects
are global
end sub

The problem is that to call this, I need to say something like:
establishRecordset ????? WHAT?

If I try Queries!qryTryIt, I see a complaint about needing an object. But I
can't find anything that lets me do the equivalent of qryTryIt.text or value,
similar to a form control's Value property.


Getting the text of a query

Post by Mark McGin » Fri, 11 Mar 2005 23:53:33

So what's being passed to this sub? The name of a querydef, or a SQL
statement? And are you planning to use this recordset within the function,
or you want to create it for the caller? I'll assume the latters of both:

One way to return a recordset object it to pass it byref to the
function/sub, then open it inside the function, but to do that you'll need
to create the recordset object before the call:

Sub ReturnByRef(str as string, ByRef rst as ADODB.Recordset)
rst.Open str, conn ' assume connection is global?
End Sub

And calling code would look like:

Set rst = New ADODB.Recordset
ReturnByRef str, rst
' test/use recordset

But that's not real useful, the other way is to declare a function and
return the object:

Function ReturnRst(str as string)
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open str, conn ' assume connection is global?
Set ReturnRst = rst
End Function

And the caller:

Set rst = ReturnRst(str)

You can get the SQL statement that underlies a recordset via its Source
property, but if you're just passing it a querydef name, I doubt you'll get
much more than that back.