Results 1 to 4 of 4

Error While running SQL Stored procedure thru Access 2010 VBA

  1. #1
    rd.prasanna is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    25

    Error While running SQL Stored procedure thru Access 2010 VBA

    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.

    Code:
    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
    Code:
    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

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,315
    cross posted here with answers

    https://www.access-programmers.co.uk...d.php?t=300625

    @prasanna - please read this link about the etiquette of cross posting http://www.excelguru.ca/content.php?184

  3. #3
    rd.prasanna is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    25
    My Apologies....

  4. #4
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,484
    Also at http://www.utteraccess.com/forum/ind...ic=2049896&hl=

    @rdprasanna
    Please post links at all other sites yourself to avoid others unaware of the multiple posts wasting time
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Stored Procedure in MS SQL Server from Access
    By jaryszek in forum Access
    Replies: 7
    Last Post: 01-17-2018, 04:58 AM
  2. Replies: 2
    Last Post: 06-01-2016, 07:52 AM
  3. Replies: 3
    Last Post: 10-27-2015, 01:37 PM
  4. Running a shell command from a stored procedure.
    By sstrauss87 in forum SQL Server
    Replies: 3
    Last Post: 03-01-2012, 03:39 PM
  5. Execute MySQL Stored Procedure with Access 2010 VBA?
    By DanielHofer in forum Programming
    Replies: 5
    Last Post: 01-23-2012, 12:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums