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

Tech-Archive recommends: Speed Up your PC by fixing your registry

sorry for not making it clearer. Its in the subject following through
to the first line.


Jan Hyde wrote:

"Karnifexx" <m.p.fletcher@xxxxxxxxxxxxxx>'s wild thoughts
were released on 9 Dec 2006 15:14:18 -0800 bearing the
following fruit:

Did you post the error your getting?

...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 most of the time (recently noticed the error only occurs when the
SP (any SP for that matter I call with this function, has more than one
parameter). I am not doing anything differently so why is the function
behaving so awkwardly?

Kind regards,
Marc


Jan Hyde (VB MVP)

--
A woman was in love with fourteen soldiers - it was clearly platoonic

.

Relevant Pages