msacces vba call procedure fails when parameter added-CodePudding

I am trying to teach myself creation and recall of stored procedures. Having searched for 2 days I have got no further resolving the following issue.

I have a basic stored procedure as follows in SQL Express:

GO
/****** Object:  StoredProcedure [dbo].[spTestCustomers]    Script Date: 26/01/2022 15:17:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[dbo].[spTestCustomers]

AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM [tblCustomers]

END

I can call this correctly with the following VB in MS Access:

Private Sub cmdTest_Click()
'define query for ptSHCustomers
    Dim SQL As String, qdf As DAO.QueryDef
        
    Set qdf = CurrentDb.QueryDefs("ptSHCustomers")

    qdf.SQL = "EXEC spTestCustomers"
    
    Me.lstTest.Requery
  
End Sub

However when I add a parameter I fail, SQL Server code:

USE [WPSDb]
GO
/****** Object:  StoredProcedure [dbo].[spTestCustomers]    Script Date: 26/01/2022 15:25:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[dbo].[spTestCustomers]
@Surname nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM [tblCustomers]
WHERE Surname like @Surname
END

The code I am trying to call with from MS Access:

Private Sub cmdTest_Click()
'define query for ptSHCustomers
    Dim SQL As String, qdf As DAO.QueryDef
    Dim VarCustSurname As String
  
    VarCustSurname = "%"
        
    Set qdf = CurrentDb.QueryDefs("ptSHCustomers")

    qdf.SQL = "EXEC spTestCustomers ('" & VarCustName & "');"
    
    Me.lstTest.Requery
  
End Sub

I have tried numerous code changes from guidance online but all to no avail. Can anyone point me in the right direction?

CodePudding user response：

EXECUTE doesn't use parentheses.

EXEC myProc @varParam, 'literal param';

and in VBA

qdf.SQL = "EXEC spTestCustomers '" & VarCustName & "';"