Unanswered: Connection Closed but still doing business?
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.
@ItemNum as varchar(25),
@ItemExtra as varchar(25)
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)
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
' 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
' Open recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CacheSize = 100000
rs.Open cmd, , adOpenStatic, adLockOptimistic
FetchSQLbyADO = True
Set cnn = Nothing
MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Fetch Error"
FetchSQLbyADO = False
However, using the exact code shown in SQL Server 2008R2 and Exel 2010, I can reproduce the creation of records where all the fields are null in the SQL server table by adding rows to the recordset in VBA.
The only thing I can figure is that when the recordset is opened in VBA, it creates it's own connection to the datasource. However, the STATIC parameter should still prevent writing data back. Not to mention the sp should be closed once it returns the original dataset.
My work around (because I was in a rush and had to move on) was to create a second recordset in Excel, copy the smallish dataset line by line to the new recordset and close the original recordset. The cost is a few milliseconds of processor time, but it worked.