I am trying to run a parameterized SQL Server stored procedure from an Excel
macro using the ADODB library ("Microsoft ActiveX Data Objects 2.8 Library").
The stored procedure only has action queries. It only accepts one input
parameter, no output parameters. I have tried numerous methods of running the
procedure with no luck. I keep getting an "automation error", "unspecified
error", but no error description. Here is the code:

Private Sub CommandButton1_Click()
' This requires a Reference to Microsoft ActiveX Data Objects 2.x Library
Const cConnection = "Provider=sqloledb;" & _
"server=finseaa16;database=rfdb;uid=rfdb_rw;pwd=xx xx"
Const cSQL = "CLS_PKG_TOP20_BLR"
Dim con As ADODB.Connection, cmd As ADODB.Command
Set con = New ADODB.Connection
con.Open cConnection 'Open connection to the database
Set cmd = New ADODB.Command
cmd.ActiveConnection = con 'Set up our command object for executing SQL
statement
cmd.CommandText = cSQL
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("InputRun", adVarChar,
adParamInput, 8, "R09SEP05")
'cmd("InputRun").Value = "R09SEP05"
cmd.Execute Options:=adExecuteNoRecords
End Sub

I have also tried using a CommandType of acCmdText and including the
parameter in the CommandText like this:

..CommandText = "EXEC CLS_PKG_TOP20_BLR 'R09SEP05'"

--
Peder Myhre