Set up a pass through query. In it run your stored procedure. Make sure you set the query properties to return results = yes. In design view it will look something like
EXEC dbo.yourSPName @iYourParameter = 12345
This would assume that you have a parameter that is an integer. It always appears that it's important to know the parameter names and call them the same things.
This will get you the SQL SP results into a query window.
If you need to dynamically change the parameters then you will need to use a query def something like;
Code:
Set db = CurrentDb
Set qdfpt = db.QueryDefs("yourPassThroughQuery")
qdfpt.Connect = "ODBC;DRIVER=SQL Server;SERVER=SQLSERVER;;DATABASE=YourSqlDatabase;UseTrustedConnection=True"
qdfpt.SQL = "exec dbo.YourStoredProcedure @YourParameter = " & Me.YourParameterOnTheForm & ""
db.Close