Error While running SQL Stored procedure thru Access 2010 VBA

[rd.prasanna]

Hi All,

I am trying to run sql stored procedure from Access form.
Which is throwing error “procedure or function has too many arguments” while I have only one parameter passing to stored procedure.

i am using sql server 2012

I have attached the stored procedure and VBA code below.

Kindly check and let me know where iam going wrong.

ALTER Procedure [dbo].[SP_SSIS_pkg_Rfnd_BSP] (@ExcelFilePath sql_variant)

As
 
 begin
 DECLARE @FolderName nvarchar(128) = 'Import_RAData_BSP'
 DECLARE @ProjectName nvarchar(128) = 'SSIS_Rfnd_App_BSP'
 DECLARE @PackageName nvarchar(260) = 'pkg_Rfnd_BSP.dtsx'
 DECLARE @LoggingLevel varchar(16) = 'Basic'
 DECLARE @Use32BitRunTime bit = 0
 DECLARE @ReferenceID bigint = NULL
 DECLARE @ObjectType smallint = 50
 DECLARE @ExecutionID bigint  
  
 Set NOCOUNT ON
   
   /* Call the catalog.create_execution stored procedure
      to initialize execution location and parameters */
  Exec SSISDB.catalog.create_execution
   @package_name = @PackageName
  ,@execution_id = @ExecutionID Output
  ,@folder_name = @FolderName
  ,@project_name = @ProjectName
  ,@use32bitruntime = @Use32BitRunTime
  ,@reference_id = @ReferenceID
 
   /* Populate the @ExecutionID parameter for OUTPUT */
  Select @ExecutionID As Execution_Id
 
   /* Create a parameter (variable) named @Sql */
  Declare @logging_level smallint
   /* Decode the Logging Level */
  Select @logging_level = Case
                           When Upper(@LoggingLevel) = 'BASIC'
                           Then 1
                           When Upper(@LoggingLevel) = 'PERFORMANCE'
                           Then 2
                            When Upper(@LoggingLevel) = 'VERBOSE'
                           Then 3
                           Else 0 /* 'None' */
                          End

   /* Call the catalog.set_execution_parameter_value stored
      procedure to update the LOGGING_LEVEL parameter */
  Exec SSISDB.catalog.set_execution_parameter_value
    @ExecutionID
   ,@object_type = 30
   ,@parameter_name = N'ExcelFilePath'
   ,@parameter_value = @ExcelFilePath


   /* Call the catalog.set_execution_parameter_value stored
      procedure to update the LOGGING_LEVEL parameter */
  Exec SSISDB.catalog.set_execution_parameter_value
    @ExecutionID
   ,@object_type = @ObjectType
   ,@parameter_name = N'LOGGING_LEVEL'
   ,@parameter_value = @logging_level
 
   /* Call the catalog.start_execution (self-explanatory) */
  Exec SSISDB.catalog.start_execution @ExecutionID
 end

VBA Function to execute stored procedure

Function Import_RA_Data(ByVal FileName As String, FName As String)
    On Error GoTo ErrHandler:

    Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objParm As New ADODB.Parameter
    Dim objRs As New ADODB.Recordset
    Dim FilePath As String
   
    ' Set CommandText equal to the stored procedure name.
    objCmd.CommandText = "SP_SSIS_pkg_Rfnd_BSP"
    objCmd.CommandType = adCmdStoredProc

    ' Connect to the data source.
    Set objConn = GetNewConnection
    objCmd.ActiveConnection = objConn

    ' Automatically fill in parameter info from stored procedure.
    objCmd.Parameters.Refresh

    objParm.Value = FilePath

    Set objParm = objCmd.CreateParameter("@ExcelFilePath", adVariant, adParamInput, , objParm.Value)
        objCmd.Parameters.Append objParm
    
    objRs.CursorType = adOpenStatic
    objRs.CursorLocation = adUseClient
    objRs.LockType = adLockOptimistic
    objRs.Open objCmd
    
    ' Execute once and display...
    Set objRs = objCmd.Execute

    'clean up
    objRs.Close
    objConn.Close
    Set objRs = Nothing
    Set objConn = Nothing
    Set objCmd = Nothing
    Set objParm = Nothing
    Exit Function

ErrHandler:
    'clean up
    If objRs.State = adStateOpen Then
        objRs.Close
    End If

    If objConn.State = adStateOpen Then
        objConn.Close
    End If

    Set objRs = Nothing
    Set objConn = Nothing
    Set objCmd = Nothing
    Set objParm = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, vbCritical, "Error"

    End If
End Function

Regards,
Prasanna

 

[JHB]

In which code line do you get the error, (comment out the error handling)?

 

[rd.prasanna]

In which code line do you get the error?

thank you for the response.

im getting error in below line

Set objRs = objCmd.Execute