How to execute a stored procedure via pass-through query

[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.

 

[Douglas J. Steele]

For the SQL, use something like:

Call MyStoredProcedure

When setting up the pass-through, make sure you indicate that the query
returns records.

 

[Guest]

Thank you, Doug.
I forgot to ask, how does one set the run time parameters? I have a
parameter that selectes a project ID

 

[Guest]

Thank you, Doug.
I forgot to ask, how does one set the run time parameters? I have a
parameter that selectes a project ID

 

[Guest]

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.

 

[Guest]

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?

 

[Sylvain Lafontaine]

You cannot have dynamic parameters with a SQL pass-through query under
Access: you must change the text of the query (the name of the property is
..SQL) so that the new value(s) become "hard-coded" before each execution.
For other examples:

http://support.microsoft.com/?kbid=232493

http://www.databasejournal.com/features/msaccess/article.php/3407531