Pass Parameter to stored procedure in pass thru query

S

SAC

How do I pass a parameter to a stored procedure in a pass thru query?

I'd like to enter the parameter in a control on a form and then run the pass
thru query and pass the parameter to it.

I've tried this, but haven't been successful.

Any ideas?

Thanks.
 
J

John W. Vinson

How do I pass a parameter to a stored procedure in a pass thru query?

I'd like to enter the parameter in a control on a form and then run the pass
thru query and pass the parameter to it.

I've tried this, but haven't been successful.

Any ideas?

You can't. The SP has no <ahem> access to the Access objects which contain the
parameter.

You'll need to construct the SQL of the pass-through query in VBA code,
concatenating the values of the parameters into the query appropriately.

The end result is the same, but you just can't pass the parameters directly.

John W. Vinson [MVP]
 
J

John W. Vinson

Thanks, John.

Would an Acess Project do the job?

Probably would be better, since it's closer to the SQL world. I've had very
little experience with them, though.

John W. Vinson [MVP]
 
S

SAC

Thanks!
John W. Vinson said:
Probably would be better, since it's closer to the SQL world. I've had
very
little experience with them, though.

John W. Vinson [MVP]
 
S

SAC

John,

I'm still a little lost.

I've made a passthrough query and tested it and it seems to work.

I have:

exec sp_archiveretrieve @thedate = '06/23/2007'

this runs when I click the ! in the query window.

If I saved this query as query16 and tried running it with this:

Dim stDocName As String

stDocName = "Query16 @thedate='06/23/2007'"
DoCmd.OpenQuery stDocName, acNormal, acEdit

I also tried making a string like this: "exec sp_archiveretrieve
@thedate='6/23/2007'" and a docmd.runsql and it doesn't work.

Any other ideas?

Thanks
 
J

John W. Vinson

John,

I'm still a little lost.

I've made a passthrough query and tested it and it seems to work.

I have:

exec sp_archiveretrieve @thedate = '06/23/2007'

this runs when I click the ! in the query window.

If I saved this query as query16 and tried running it with this:

Dim stDocName As String

stDocName = "Query16 @thedate='06/23/2007'"
DoCmd.OpenQuery stDocName, acNormal, acEdit

I also tried making a string like this: "exec sp_archiveretrieve
@thedate='6/23/2007'" and a docmd.runsql and it doesn't work.

You're mixing levels. Putting the name Query16 in the string and passing that
to SQL won't work, because SQL has no way to look into your Access app and
find Query16.

I think you'll need to use the CreateQuerydef method to actually create a
passthrough query - the passthrough is a property of the query; or save
Query16 (renamed to something meaningful!!!) and use VBA code to edit its SQL
property before executing it.

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top