How to execute a stored procedure via pass-through query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a stored procedure on a SQL server database that I would like to
execute and return records to my Access client. The help file got me to the
point that I can create a pass through query, but I can't find documentation
on how to format the stored procedure call. I deally, I would like it to
work just like a query in that it would create a query table that I can use
in another query and finally a report. Any suggestions will be greatly
appreciated.
 
For the SQL, use something like:

Call MyStoredProcedure

When setting up the pass-through, make sure you indicate that the query
returns records.
 
Thank you, Doug.
I forgot to ask, how does one set the run time parameters? I have a
parameter that selectes a project ID
 
Thank you, Doug.
I forgot to ask, how does one set the run time parameters? I have a
parameter that selectes a project ID
 
Thanks again, Doug.
It turns out the "call" is unnecessary and creates an error. Just the
stored procedure name is sufficient in a pass-through query. I still can't
figure out how to set a parameter,ever. My stored procedure is defined as
follows...

CREATE PROCEDURE usp_portal_deliverables
@pnProjectID INT = NULL,
@pvTypeCode CHAR(1) = 'P'
/***********************************************************....etc, etc.

I want to set the @pnProjectID to a value. Right now it just returns the
results for rows without projects.
 
Sorry, for all of the replies, but I continue to experiement.
Apparently, all one has to do in the pass-through query window in ACCEss is
something like this...

usp_portal_deliverables @pnProjectID = 35

However, I want the '35' to be passed into the Access query rather than
hardcoded, as I have another query that determines the Project ID.

Suggestions?
 
Back
Top