View The Output of a SQL Server Stored Procedure in Access

Bind the subform to a pass-through query.  

PS - I think the sp must have an argument since Access won't sync subforms bound to pass-through queries.  Also, the recordset will not be updateable.

thank you Nick67, what if i want to pass the parameter to the procedure dynamically at run time.

I do that all the time.
Build the valid T-SQL string in VBA
fire up a querydef of the passthrough
change the .SQL property
Go to town

Dim db As Database
Dim QDF As QueryDef
Dim rs As Recordset
Dim SQL As String
Dim myJobID as Long

Set db = CurrentDb
Set QDF = db.QueryDefs("pthrMyInspectors")
myJobID = Forms!frmJobID!JobID
SQL = "exec spMyInspectors  " & myJobID
QDF.SQL = SQL
Set rs = QDF.OpenRecordset(dbOpenDynaset, dbSeeChanges)

'go to town.
'At this point the passthrough has been permanently altered, so if it is bound to a form/object, requerying will refresh the data

Generally, I use DAO for things.
ADO in certain circumstances can have a clear performance benefit.
You can bind objects to ADO recordsets drawn from stored procedures too will syntax like this

Dim dbCon As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
dbCon.ConnectionString = Forms!tblDummy!ADOConnString.Value 'I store the connection string in table. This control has that string
dbCon.Open
dbCon.CursorLocation = adUseClient
With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "QryFREquipType"
    .Parameters.Append .CreateParameter("@GeneralEquipTypeID", adInteger, adParamInput, , 23)
    .ActiveConnection = dbCon
    .NamedParameters = True
    Set rst = .Execute
End With

'bind a combobox to it as a test
Set Me.Combo24.Recordset = rst
'done with ADO, clean up
dbCon.Close
Set dbCon = Nothing
Set cmd = Nothing
Set rst = Nothing

Select allOpen in new window