Passing a variable from Access (frontend) to SQL (backend) Stored Procedure

There may be a more direct method, but you could:

1. Make a 1 row, 1 column table in SQL Server.

2. Before executing your stored procedure, your Access db can clear that table and insert your value to it (using standard Delete and Insert queries).

3. Then, your stored procedure can access that value (or values) with a simple Select.

You can use VBA to create a QueryDef that executes a pass-through query. For example, I have a database named [myDb] on my SQL Server, and I have created an ODBC System DSN named myDb so Access can connect to it. The database contains a table named [myContacts]...

ID  LastName
 1  Thompson
 2  Gumby

...and a stored procedure named [getContact]

CREATE PROCEDURE getContact 
    -- Add the parameters for the stored procedure here
    @id int = 1
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * FROM myContacts WHERE ID=@id
END

The following VBA code creates a pass-through query, executes the stored procedure with a parameter identifying the ID number to process, and saves the returned row in a Recordset:

Sub spTest()
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim IdValueToProcess As Long

IdValueToProcess = 2  ' test data

Set qdf = CurrentDb.CreateQueryDef("")
qdf.ReturnsRecords = True
qdf.Connect = "ODBC;DSN=myDb;Trusted_Connection=Yes;"
qdf.SQL = "EXEC dbo.getContact " & IdValueToProcess
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

Debug.Print rst!LastName  ' just to make sure we got a result

rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing
End Sub