Cannot change the ActiveConnection property of a recordset object....

[m.p.fletcher]

....which has a command object as its source.

Is the error mesage that is currently driving me nutty. Let me explain.
The function causing the problem is as follows:

Public Type SPDataset
Name As String 'Name of the stored procedure
arrParameters() As String
Source As String 'The calling Form
bSuccess As Boolean 'If everything went ok
End Type

Public Function sqlSP(ExecuteSP As SPDataset) As ADODB.Recordset

Dim sqlConn As ADODB.Connection
Dim sqlCmd As ADODB.Command
Dim sqlRS As ADODB.Recordset
Dim N As Integer

' Establish connection.
Set sqlRS = New ADODB.Recordset
Set sqlCmd = New ADODB.Command
Set sqlConn = New ADODB.Connection

'Make sure the project is connected to the server
If (CurrentProject.IsConnected = False) Then
'Stop the form from loading
MsgBox "You must be logged in to perform this action. Please
login and try again.", vbExclamation, "Database Error"
ExecuteSP.bSuccess = False

Else

sqlConn.ConnectionString = CurrentProject.Connection
sqlConn.Open

sqlConn.CursorLocation = adUseClient 'Cache data locally

' Open recordset.
With sqlCmd
.ActiveConnection = sqlConn
.CommandText = ExecuteSP.Name
.CommandType = adCmdStoredProc
.Parameters.Refresh
'Load the parameters
If IsArrayAllocated(ExecuteSP.arrParameters) Then
For N = LBound(ExecuteSP.arrParameters) To
UBound(ExecuteSP.arrParameters)
.Parameters("@" & ExecuteSP.arrParameters(N,
0)).Value = ExecuteSP.arrParameters(N, 1)
Next N
End If
Set sqlRS = .Execute()
End With

'Set the output parameters if they are requested
If IsArrayAllocated(ExecuteSP.arrParameters) Then
For N = LBound(ExecuteSP.arrParameters) To
UBound(ExecuteSP.arrParameters)
If Not ExecuteSP.arrParameters(N, 2) = "" Then
If ExecuteSP.arrParameters(N, 2) = True Then
ExecuteSP.arrParameters(N, 1) =
sqlCmd.Parameters("@" & ExecuteSP.arrParameters(N, 0))
End If
End If
Next N
End If

Set sqlRS.ActiveConnection = Nothing
sqlConn.Close
Set sqlConn = Nothing

Set sqlSP = sqlRS

Set sqlRS = Nothing
Set sqlCmd = Nothing
ExecuteSP.bSuccess = True

End If

End Function

The idea behind it was that I would simply pass this function the
required parameters and it would pass back a disconnected recordset. It
works 90% of the time, with and without passed parameters but sometimes
I get the aforementioned error message. I am not doing anything
differently so why is the function behavining so awkwardly?

Kind regards,
Marc

 

[m.p.fletcher]

I believe the error message is occuring when there is more than one SP
parameter, since all SP's taking a single parameter run just find (i.e.
the 90% of the time I refered to in my previous post).