RE: Function not returning a recordset

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" wrote:

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

.