Access 365 - Object Required Error, Passing String to cmd.Parameters: Item Cannot be Found in Collec-CodePudding

UPDATE 2: I uh... may have forgotten to uncomment this line...

 If Not IsMissing(Arg1) Then
        cmd.CreateParameter(1, adVarChar, adParamInput) = Arg1 
       'cmd.Parameters(1) = Arg1 <--
 End If

First run after uncommenting worked... this is a good sign. Will continue testing and report back. When I added the CreateParameter line before, I hadn't checked the user permission so that might have been the issue. Between both of these, it might be solved. Stand by!

UPDATE: Added the line below and got a new error: -2147217911 The EXECUTE permission was denied on the object-2147217904 Procedure or function 'DW_LocalAccount_Read' expects parameter '@RowID', which was not supplied.

Went to the the server the SP is on and looked at the user the Access DB uses, and it had read/write access, but I gave it owner for testing and then the error changed to: -2147217904 Procedure or function 'DW_LocalAccount_Read' expects parameter '@RowID', which was not supplied.

So that narrows it some, I have to figure out why the parameter is not being passed.

 If Not IsMissing(Arg1) Then
        cmd.CreateParameter(1, adVarChar, adParamInput) = Arg1 <--
       'cmd.Parameters(1) = Arg1
 End If

ORIGINAL QUESTION POST

I've spent a day and a half on this reading hundreds of posts about "Object Required" and "Item not found in this collection or ordinal" errors, but nothing seems to quite match my issue.

I inherited an Access 365 DB from someone who no longer works here. There's a form that holds customer data, and it comes in through a stored procedure in SQL Server. Front end Access user asked that data in Ship1 be entered into Ship3 when ship3 is blank. In the SP I added a case statement to do just that which works fine.

When I went back to the Access front end however, it's now throwing an Object Required error on:

Set rsLocal = FormHandling.GetFormRecordset("DW_LocalAccount_Read", RowID)

Assuming the issue was an error in the SP, I verified that all the columns were pulled and in the same order, the names were all correct etc. etc. So that the result set would be the same as before (with the exception of ship3) So then, I swapped back to the old version of the SP while I worked on the modified version and that doesn't work now either.

So then I stepped through in Access to find where the error starts and it seems to come from here:

    If Not IsMissing(Arg1) Then
        **cmd.Parameters(1) = Arg1** <--
    End If

When I hit that line, it errors out, goes back to the original Set statement and throws the object required error. I threw in a msgbox to find the error and it came up as "3265 item cannot be found in the collection corresponding to the requested name or ordinal"

The entire function is:

Public Function GetFormRecordset(CommandText As String, Optional Arg1 As Variant, Optional Arg2 As Variant, Optional Arg3 As Variant, Optional Arg4 As Variant, Optional Arg5 As Variant) As Variant
    
    On Error GoTo Function_Error
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim parm As ADODB.Parameter
    Dim rsDBO As DAO.Recordset
    
    cmd.CommandType = adCmdStoredProc
    cmd.NamedParameters = True
    cmd.Prepared = True
    cmd.CommandText = CommandText
    
    Set cmd.ActiveConnection = DWDB
    cmd.Parameters.Refresh
                   
    If Not IsMissing(Arg1) Then
        cmd.Parameters(1) = Arg1
    End If
    
    If Not IsMissing(Arg2) Then
        cmd.Parameters(2) = Arg2
    End If
    
    If Not IsMissing(Arg3) Then
        cmd.Parameters(3) = Arg3
    End If
    
    If Not IsMissing(Arg4) Then
        cmd.Parameters(4) = Arg4
    End If
    
    If Not IsMissing(Arg5) Then
        cmd.Parameters(5) = Arg5
    End If
    
    Set rs = New ADODB.Recordset
    With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        '.LockType = adLockOptimistic
        .LockType = adLockReadOnly
        .Open cmd
        .MoveLast
        .MoveFirst
    End With
    
    Set GetFormRecordset = rs
    
    
Function_Exit:
    On Error Resume Next
    'rs.Close
    'Set cmd = Nothing
    'rsDBO.Close
    'Set rsDBO = Nothing
    On Error GoTo 0
    Exit Function
    
Function_Error:
    If Err.Number = 3709 Then
        'no valid conn
        Set GetFormRecordset = Nothing
    ElseIf Err.Number = 3021 Then
        'EOF/BOF
        Resume Next
    End If

End Function

I'm kinda new to working with parameters and collections; so I tried to read up, and tried appending the parameter before the "=arg1" section, but that didn't work. I feel like I'm missing something, why did the original SP work, but then when I did nothing but swap data from one column to another break it? And then why did the original SP work before and not now when no Access code was changed? Any help would be appreciated as I feel like I'm beating my head against a wall on this one.

Oh here's the original SP also:

USE [DB]
GO

/****** Object:  StoredProcedure [dbo].[DW_LocalAccount_READ]    Script Date: 8/16/2022 11:31:39 AM ******/
DROP PROCEDURE [dbo].[DW_LocalAccount_READ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
    
CREATE PROCEDURE [dbo].[DW_LocalAccount_READ]
    @RowID as Varchar(38)   
AS 
    SET NOCOUNT ON
BEGIN TRY

        BEGIN
            SELECT RBCCustDtl.*
            FROM RBCCustDtl WITH(NOLOCK)
            Where 
                (
                RBCCustDtl.RBCCustDtlCustDtlRowID = Cast(@Rowid as uniqueidentifier)
                Or 
                @RowID = ''
                )
        END
    END TRY

BEGIN CATCH     
    IF @@ERROR <> 0
        BEGIN
            Return null
        END
END CATCH
GO

CodePudding user response：

The issue ending up being entirely a server permissions issue. Access DB user had permissions to read/write to the DB, but When I dropped/created the procedure the user lost execute rights to the SP. Once I granted execute on the SP everything fell into place including the modifications on the SP.