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.

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

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Quartz" skrev i melding
...
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.

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

-----Original Message-----
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

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Quartz" skrev i
melding
...
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.



.

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...)

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Quartz" skrev i melding
...
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

-----Original Message-----
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

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Quartz" skrev i
melding
...
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.



.

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" wrote in message
...
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

-----Original Message-----
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

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Quartz" skrev i
melding
...
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.



.

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

Function RunADOQuery(args) As ADODB.Recordset


-----Original Message-----
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...)

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Quartz" skrev i
melding
...
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

-----Original Message-----
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

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Quartz" skrev i
melding
...
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.



.



.