SQL query with input parameters (stored procedure) (1 Viewer)

there is a stored procedure that I'm trying to tap into. input parameters have already been setup, but querying (using the input parameters) this stored procedure seems to only work in Excel.

I would like to bring that query into Access, but allow the user to change the input parameters.

for example, when viewing the SQL from Excel, the command text is: {call storedproc2 (?,?,?,?,NULL,NULL,NULL,?,?,?)}

the only way that i have found to get this to run in Access is to setup a Pass-Thru Query and hard code the ? to the parameters that I want. for example: {call storedproc2 (123456,'MD',1234,'USA',NULL,NULL,NULL,'BAL',0,0)}

preferably, I'd like those ? to be replaced with values from fields on a form.

so essentially, how can I get the 123456 input parameter to reference a form field rather than having the user edit the query each time?

I've tried using the [Forms]![Frm_Rpt]![Text1], but that did not work.

thanks!

Hi. I could be wrong but I think to use a pass-thru query in Access with dynamic parameters might require the use of a QueryDef object where you'd replace the SQL statement after building a new one where you insert the parameter values in the proper places.

theDBguy said:

Hi. I could be wrong but I think to use a pass-thru query in Access with dynamic parameters might require the use of a QueryDef object where you'd replace the SQL statement after building a new one where you insert the parameter values in the proper places.

Click to expand...

That is exactly what I do. Searching on "passthroughfixup" will probably find a function that handles it. I got mine out of a book so don't feel I can post it publicly.

USe something like ;

Obviously you'll need to reference your forms correct controls and handle the possible null values, but this is a simple way to handle it.

As Paul advised you could chuck this type of construct into a function to create a temporary query and execute it.

Minty said:

As Paul advised you could chuck this type of construct into a function to create a temporary query and execute it.

Click to expand...

I don't create a temporary query, I replace the SQL of a saved query. The query name is one of the arguments of the function.

@Paul - apologies I misinterpreted what you wrote!

No worries, it wasn't that clear, just wanted to make sure the OP knew it could be used either way. I'm often using the pass through as the source of a report or something, so not just executing SQL.

That's certainly probably more common.

I only have a couple of SP that I run via Access, and they are to used create complex temp tables to run reports from, mainly as a way to speed up the processing.