This is how I execute SQL stored procedures in MS Access (VBA):

'ADODB connection (to SQL Server).
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
With cnn
    .ConnectionString = {your ODBC connection to SQL Server}
    .CommandTimeout = 30 '<-- optional, but speeds up connection problems.
    .Open
End With

'ADODB command (reference to SQL stored procedure)
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = cnn
    .CommandText = {your_stored_procedure_name}
    .CommandType = adCmdStoredProc
    .CommandTimeout = 0
    .Parameters.Refresh
End With

'Add your SP parameter(s)...
cmd.Parameters("@param_name").value = {your_value}

'ADODB recordset to capture your SP results.
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .CacheSize = 1
    .LockType = adLockReadOnly
    .Open cmd
End With

'Loop through the resultset...
Do While Not rs.EOF

    'Work with your resultset here...

rs.MoveNext '<-- don't forget this!
Loop

'Cleanup.
rs.Close
Set rs = Nothing

You can use this:

Dim strName   as string
dim rst    as DAO.RecordSet
strName = [Forms]![Search]![SearchFirstName]
strSQL = "SELECT [First name], [Surname] FROM [Names] WHERE SurName = '" & strName & "'"

CurrentDb.QueryDefs("MyPTQuery").SQL = strSQL

' now open report based on above.

Or for a reocrdset, go:

dim rst    as DAO.RecordSet
set rst = CurrentDB.OpenRecordset("MyPTQuery")

A bit of an update to my original question. By using the help from here, I have managed to get the query to work for one variable. It works as follows:

SQL Stored procedure:

ALTER PROCEDURE [dbo].[ZSearch]

@First varchar(100)

AS
BEGIN

SELECT [First name], [Surname]
FROM [Names]
Where  ([First Name] LIKE '%' + @First + '%')
END

Then in Access, I have a pass through query which is:

exec [ZSearch]

Then Finally, on my form, I have a button which runs the following VB on click:

Private Sub Command4_Click()
Dim rst As DAO.Recordset

Dim First As String

First = [Forms]![Search]![SearchFirstName]

   
With CurrentDb.QueryDefs("qryPassR")
    .SQL = "exec ZSearch " & First
'Debug.Print .SQL
    Set rst = .OpenRecordset()
    DoCmd.OpenQuery "qryPassR"
End With
End Sub

As I say this works really well. It gets the results that I want it too. However, I would also like to add extra search variables, for example SURNAME. I would also need it to work, if any of the text boxes in Access were null, or somehow set their default value to a wildcard.

I have tried the following

SQL Stored Procedure

ALTER PROCEDURE [dbo].[ZSearch]

@First varchar(100),
@Second varchar(100)

AS
BEGIN

SELECT [First name], [Surname]
FROM [Names]
Where  ([First Name] LIKE '%' + @First + '%') and ([Surname] LIKE '%' + @Second + '%')
END

Then in Access, I have a pass through query which is:

exec [ZSearch]

Then Finally, on my form, I have a button which runs the following VB on click:

Private Sub Command4_Click()
Dim rst As DAO.Recordset

Dim First As String

First = [Forms]![Search]![SearchFirstName]
Second = [Forms]![Search]![SearchSurname]

   
With CurrentDb.QueryDefs("qryPassR")
    .SQL = "exec ZSearch " & First & Second
'Debug.Print .SQL
    Set rst = .OpenRecordset()
    DoCmd.OpenQuery "qryPassR"
End With
End Sub

However, when I try adding the second variable it does not work, and the vba debugger says 'Compile Error'

I am hoping that it is something simple which I am doing wrong