How to execute a stored procedure via pass-through query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Crisp - 29 Jun 2005 01:32 GMT

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.

Reply to this Message

Douglas J. Steele - 29 Jun 2005 01:40 GMT

For the SQL, use something like:

Call MyStoredProcedure

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>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
[quoted text clipped - 6 lines]
> in another query and finally a report. Any suggestions will be greatly
> appreciated.

Reply to this Message

Crisp - 29 Jun 2005 17:00 GMT

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

> For the SQL, use something like:
>
[quoted text clipped - 13 lines]
> > in another query and finally a report. Any suggestions will be greatly
> > appreciated.

Reply to this Message

Crisp - 29 Jun 2005 17:01 GMT

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

> For the SQL, use something like:
>
[quoted text clipped - 13 lines]
> > in another query and finally a report. Any suggestions will be greatly
> > appreciated.

Reply to this Message

Crisp - 29 Jun 2005 17:25 GMT

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.

> For the SQL, use something like:
>
[quoted text clipped - 13 lines]
> > in another query and finally a report. Any suggestions will be greatly
> > appreciated.

Reply to this Message

Crisp - 29 Jun 2005 17:38 GMT

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?

> For the SQL, use something like:
>
[quoted text clipped - 13 lines]
> > in another query and finally a report. Any suggestions will be greatly
> > appreciated.

Reply to this Message

Sylvain Lafontaine - 30 Jun 2005 06:09 GMT

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

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> 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
[quoted text clipped - 27 lines]
>> > in another query and finally a report. Any suggestions will be greatly
>> > appreciated.

Reply to this Message