SQL2K, Excel VBA using ADO: When command.ActiveConnection = gcn, it always contains a parameter with it. How to find and remove?
Posted on 2007-11-28
In Excel VBA I am using ADO to connect to a SQL2K db, and set a recordset object = SP with one parameter
The SP works fine in QA
cmdPLCY.CommandType = adCmdStoredProc
cmdPLCY.CommandText = "spm_POLICY_INFO"
cmdPLCY.ActiveConnection = gcnCASES '<-- Looky Here
After execution of the third line above, when I eyeball the Locals window in cmdPLCY.Parameters, it contains one prarameter of @RETURN_VALUE. I use this in SP's alot, but I did not set it anywhere in VBA code, and have no idea how it could be attaching itself to my connection object.
The sub that sets my connection object (below), is working fine:
Public Function scnCASES() As Boolean
'some stuff omitted
Dim sConnect As String
sConnect = "PROVIDER=SERVER;Driver=SQLServer;Server=SERVER_NAME;UID=USER_NAME;PWD=PASSWORD;Database=DB_NAME;"
If gcnCASES Is Nothing Then
Set gcnCASES = CreateObject("ADODB.Connection")
gcnCASES.ConnectionString = sConnect
If gcnCASES.State <> adStateClosed Then
scnCASES = True
scnCASES = False