Pass an ADO recordset

[Quartz]

Hello.

Is is possible to structure VBA code so that I can call a
function that runs a query, say in ADO, return the data to
a recordset and then return that recordset to the calling
subroutine in another module?

If so, I need some example code that illustrates how to do
this. I have been unsuccessful so far. My call to the
function works and the query returns the data into the
recordset, but I can't seem to pass the recordset back to
the calling subroutine. Apparently, you can't pass it like
an array? ...Unless I not dimensioning my variables
correctly???

Your example code would be most helpful. Thanks in advance.

 

[Harald Staff]

Hi

If you set reference to ADO (Microsoft ActiveX Data Objects) then you have a
datatype called ADODB.Recordset that I believe you can pass like this:

Sub test()
Dim Cnn As New ADODB.Connection
Dim Rst As New ADODB.Recordset
'Open db, Get data, ...
Call Another(Rst)
Cnn.Close
Set Rst = Nothing
Set Cnn = Nothing
End Sub

Sub Another(Rst As ADODB.Recordset)
'do stuff
End Sub

 

[Quartz]

Thanks Harald! My preferred structure would be more like
the following - is something like this possible?:

Sub Main_Sub()

....other code...

'Call query function and return recordset
rst = RunADOQueryFunction(strFullName, strSQL, strTable)

....do stuff with rst...

rst.close

end sub

 

[Harald Staff]

Probably. Datatypes are datatypes. Try making something like

Function RunADOQuery(whatever as whatever) As ADODB.Recordset
Dim Cnn As New ADODB.Connection
'...

and

Sub Main_Sub()
Dim Rst As ADODB.Recordset
'...
Set Rst = RunADOQueryFunction(variables...)

 

[Bob Phillips]

Using Harald;s example, could you not just use

Function RunADOQueryFunction(strFullName, strSQL, strTable)
Dim Cnn As New ADODB.Connection
Dim Rst As New ADODB.Recordset
'Open db, Get data, ...
RunADOQueryFunction =Rst
Cnn.Close
Set Rst = Nothing
Set Cnn = Nothing
End Sub

Sub Main()

rst = RunADOQueryFunction(strFullName, strSQL, strTable)

End Sub

This assumes you know how to connect and issue the SQL and get the recordset
back. Or is that the real question ?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[quartz]

Thanks Harald (and all who responded), I was missing
the "As ADODB.Recordset" outside the parentheses.

Function RunADOQuery(args) As ADODB.Recordset