Query a stored procedure using VBA

 

I have a stored proc that test data conversion in a new database.

I need to loop through several parameters that I pass to the stored proc.

My code is below it returns an error no results.

When I run the same sql statement in sqlServer it runs.

What am I doing wrong?

 

sub  TestDatabaseConnection_Johns_Proc

  ' Create a new Connection object

  Set AConnection = ADO.CreateConnection

  ' Note that you can also create an ADO connection using the following code:

   Set AConnection = CreateObject("ADODB.Connection")

 

  ' Specify the connection string

   AConnection.ConnectionString =  DBTables.validate_exclusions.ConnectionString

  '   msgBox(AConnection)

  

  ' Activate the connection

   AConnection.Open

  ' Create a new Command object

   Set Cmd = ADO.CreateCommand

  ' To create an ADO command you can also use the following code:

 

  ' Specify the connection

   Cmd.ActiveConnection = AConnection

  ' Specify command type and text AKA SQL sting

  Cmd.CommandText =  " declare @ERROR_FLAG varchar(1)   " & _   

              " EXEC [procQACheckFinalRiskComp]  " & _

                    "  @modelDefinitionName ='sometext',  " & _

                    "  @cohortIdNumber='310',  " & _

                    "  @ruleUsageTypeName ='somemoretext', " & _

                    "   @riskColumn='d48242', " & _

                    "  @modelYear ='2011', " & _

                    "   @serviceLine ='text', " & _

                    "   @ERROR_FLAG = @ERROR_FLAG OUTPUT " & _

                    " select @ERROR_FLAG "

 

   ' sets timeout to off                         

   Cmd.CommandTimeout = 0

    Cmd.CommandType = adCmdText

  

  ' Execute the command

  Set RecSet = Cmd.Execute

 

 

 

 

  RecSet.MoveFirst

  While Not RecSet.EOF

 

    Log.Message RecSet(0).Value

    

    RecSet.MoveNext

  WEnd

  AConnection.Close

End sub