Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Parameter and OpenstoredProcedure?

Parameter and OpenstoredProcedure?

Parameter and OpenstoredProcedure?

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?

RE: Parameter and OpenstoredProcedure?

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.

RE: Parameter and OpenstoredProcedure?

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.

RE: Parameter and OpenstoredProcedure?

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

RE: Parameter and OpenstoredProcedure?

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.

RE: Parameter and OpenstoredProcedure?

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

RE: Parameter and OpenstoredProcedure?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close