Function not returning a recordset

[RDunlap]

I'm trying to create a function that will return a recordset, but everytime I
try to access the recordset, it says it is closed. ExecSP runs a stored
procedure that returns records. Here is my code:

From the calling procedure:

Dim myResults As ADODB.Recordset
Set myResults = ExecSP("usp_SAS")
If Not myResults.BOF And Not myResults.EOF Then <----- ERROR GENERATED
myResults.MoveFirst
End If

Do While Not myResults.EOF
Debug.Print myResults.Fields(0)
myResults.MoveNext
Loop


The code for ExecSP:

Public Function ExecSP(sSPName As String) As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim cmd1 As ADODB.Command
Dim rstSPResults As ADODB.Recordset

Set rstSPResults = New ADODB.Recordset
rstSPResults.CursorLocation = adUseClient
rstSPResults.CursorType = adOpenStatic


Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "DSN=SimpleTest;"
.Open
End With
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = cnn

cmd1.CommandText = sSPName
cmd1.CommandType = adCmdStoredProc
cmd1.Parameters.Refresh

Set rstSPResults = cmd1.Execute
Set ExecSP = rstSPResults

rstSPResults.Close
Set rstSPResults = Nothing
cmd1.ActiveConnection.Close
Set cmd1 = Nothing
Set cnn = Nothing

End Function

 

[Jim Burke in Novi]

There's no reason to create an intermediate recordset. Just use

Set ExecSP = cmd1.Execute

I do this and it works fine. You might want to try doing that. I'm not sure
what you're doing with the connection though. Is that really a valid
connection? I just set mine to the application's active connection
(CurrentProject.Connection).

 

[RDunlap]

Thanks for the info and quick response Jim. I tried removing the
intermediate recordset as you suggested (I don't know why I was using it to
begin with - brain fart I guess).

Unfortunately, I still get the error. Is it because I am cleaning up and
closing the Cmd1 and cnn by chance? with these statements in the function:

cmd1.ActiveConnection.Close
Set cmd1 = Nothing
Set cnn = Nothing

 

[Stuart McCall]

I'm trying to create a function that will return a recordset, but
everytime I
try to access the recordset, it says it is closed. ExecSP runs a stored
procedure that returns records. Here is my code:

rstSPResults.Close
Set rstSPResults = Nothing

Remove these lines from your function. When you return the recordset from
your function, what you're actually returning is a POINTER to rstSPResults,
not a NEW recordset. So if you close rstSPResults, the resulting recordset
is also closed, because it's actually the SAME recordset.

 

[RDunlap]

Thanks Stuart, that worked.

I didn't know that it was just a pointer to the recordset, I thought it was
a different recordset completely. Now that you explained it, it makes sense.

 

[Jim Burke in Novi]

What does your function look like now? And why do you create a new
connection? Is the data source something other than the DB where your
application tables reside? The way you have it, I'm not sure you want
'cmd1.ActiveConnection.Close' there. Maybe that's the problem?