Parameter and OpenstoredProcedure?

[lameid]

Maybe I am missing something here but there does not seem to be a way to pass a parameter to a stored procedure and just open the query.

For example something like the below generates the error
Microsoft Office Access can't find the object 'ObjectName Parameter'

DoCmd.OpenStoredProcedure "ObjectName " & Me!Parameter

In this case the parameter is an int.

Is there an alternative or workaround?

 

[cmmrfrds]

I don't use macros so I am not sure but you could try resolving everything into a variable and using that.

Dim var1 as String
var1 = "ObjectName " & Me!Parameter

DoCmd.OpenStoredProcedure var1

Otherwise the Command Object will work for Stored Procedures.

 

[lameid]

Naturally, the variable does not help.

And looking at the command object, at best it returns a recordset. It does not open a resultset for viewing.

For those not familiar with my example I am trying to effectively open a 'query' from a database window and provide a parameter via code.

 

[lameid]

I never did find a solution to this one... I am hoping posting a reply gets some attention at the top of the pile.

 

[dashen]

I am looking for information on this as well. It looks like you may have to set up a View, instead of a SP or Function.

The View is a temporary static table from the main tables. Then you can apply your critieria to it.

Man, I think I may just develop this program on another platform if these weird things keep popping up.

I am using SQL Server 2005, so the issue is compounded by version problems.

 

[lameid]

I never did find a solution but this is the only one I didn't find a workaround for. Technically, I guess I could use a form in datasheet view and filter it (or use input parameters). But I'm not sure that would make it easy to copy and paste the data like a normal data sheet... I guess I'll end up trying it eventually. My need was not urgent.

I did not find quite a few things answered here so I ended up responding to my own questions a lot... You might have some luck looking over my questions and answers.

On the flip side, I am currently running SQL 2000, so if you have any insights about 2005 I would find that useful. I would like to get us moved over to 2005 but I am not sure what kinds of issues to expect (other than the security model is different).

 

[lameid]

I ended up using an input parameter on a form and opening it in datasheet view. I never would have thought I would use the acFormDS (why when you can just use run a query).