Hello,

Not sure if this is the right place, so I will post in both: SQL Server and Excel

I have an sp in SQL Server that catches 2 parameters, does a lookup, and returns the result.

I have a procedure in Excel that opens an ADO connection STATIC, calls the stored procedure with the parameters attached, and fields the result in a local ado recordset. It then closes the connection.

My understanding was that closing the connection orphans the recordset, and that the static parameter would makes the connection read only anyway. I also understood that the sp would return a discrete set of data, not an open connection to the tables.

I have found that these understandings are not correct, because adding data in the local recordset is inserting values into the SQL server tables. This should not be happening.

SQL Server:
Code:
PROCEDURE [dbo].[sp_Ups_ItemExtraData]
	@ItemNum as varchar(25),
	@ItemExtra as varchar(25)

AS
	
BEGIN
	
	SET NOCOUNT ON;	
	SELECT dbo.Items.ItemNo As Item, dbo.ItemExtralineDetails.DescriptionMed As Detail, dbo.ItemExtra.DescriptionMed As Value
	FROM   dbo.Items INNER JOIN
		   dbo.ItemExtra ON dbo.Items.ItemID = dbo.ItemExtra.ItemID INNER JOIN
		   dbo.ItemExtralineDetails ON dbo.ItemExtra.ItemExtralineDetailID = dbo.ItemExtralineDetails.ItemExtralineDetailID
	WHERE (dbo.ItemExtralineDetails.DescriptionMed = @ItemExtra) AND (dbo.Items.ItemNo = @ItemNum)
END
Excel:
Code:
Public Property Get FetchSQLbyADO(sproc As String, rs As ADODB.Recordset, Optional pars As ADODB.Parameters) As Boolean

    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim par As ADODB.Parameter

    
    On Error GoTo FetchError

    ' Establish connection
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = ConnectionString
    cnn.Open

    ' Setup command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandText = sproc
    cmd.CommandType = adCmdStoredProc
    If Not pars Is Nothing Then
        For Each par In pars
            cmd.Parameters.Append par
        Next
    End If

   ' Open recordset
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.CacheSize = 100000
    rs.Open cmd, , adOpenStatic, adLockOptimistic

    FetchSQLbyADO = True

FetchDone:
    Set cnn = Nothing
    Exit Property

FetchError:
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Fetch Error"
    FetchSQLbyADO = False
    Resume FetchDone

End Property
What am I doing wrong?