Parameters with DoCmd.OpenStoredProcedure

[Sid100]

I have a the following stored procedure:

Alter Procedure GetResults @Get_Id INT
As
SELECT * FROM TBLSAMPLE
WHERE ID =@Get_Id

I am calling the stored procedure using the following:
DoCmd.OpenStoredProcedure "GetResults", acViewNormal

It is possible to call the stored procedure using parameters as I need to send a parameter for @Get_Id

I know that I can use this code
strSend = "exec GetResults @GET_ID=" + "1"
CurrentProject.Connection.Execute strSend
But I need the result back in a grid format. I would appreciate any help offered

 

[Harpz]

Sid,

Try using global variables for assigning variables to the @Get_Id.

 

[Sid100]

Thankyou for your response, how could I assign a global variable for @Get_Id in Access, because as far as I am aware @Get_Id has to be declared witin the stored procedure and not at global level. I look forward to your reply.

 

[mvpacc]

Sid:

There's no practical way to send a parameter to a stored procedure and open it visually in a grid using the OpenStoreProcedure method. (BTW, global vars won't do a thing) What you need to do is revise your SP to output to a table and then open that object. If you want to use a temp table in your DB it has to be a global temp table and you have to use another non-parameterized sp to read out of that temp table; so the easiest way to do it is to have a pre-prepared table that is appended to an then open that object.

The only issue is that in a multi-user environment, you probably want to go with a temp table which requires much more work. However, there's a tip on our web that shows how to create user unique temp tables at run time. The tip is the current default tip in our Code and Design tips area and is targeted toward reports but you'll get the idea.

MVPACC

http://ourworld.compuserve.com/homepages/attac-cg

 

[Sid100]

Thanks for your help mvpacc, I have looked at your web site and the code you mentioned is very helpful. I will need to revise my VBA code and make the necessary changes to use Temporary tables. It is a shame that Access does not cater for the ability to open a stored procedure with parameters, it would save developers a lot of work.