SQL query with input parameters (stored procedure)

[jco23]

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!

 

[theDBguy]

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.

 

[pbaldy]

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.

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.

 

[Minty]

USe something like ;

    Dim db               As DAO.Database
    Dim rs               As DAO.Recordset
    Dim qdfpt            As QueryDef


    Set db = CurrentDb
    Set qdfpt = db.QueryDefs("passYourStoredPassThroughQuery")
   ' qdfpt.Connect = "ODBC;" & sConnString  'You normally won't need this unless connecting to a different database
   ' {call storedproc2 (123456,'MD',1234,'USA',NULL,NULL,NULL,'BAL',0,0)}
    qdfpt.SQL = "exec dbo.storedproc2 " & Me.NumberControl & " , '" & Me.TextControl & "' , '" & Me.txtCont" & "' ;"     'etc etc
    
    db.Close

    DoCmd.OpenQuery "passYourStoredPassThroughQuery", acViewNormal, acReadOnly

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.

 

[pbaldy]

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

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.

 

[Minty]

@Paul - apologies I misinterpreted what you wrote!

 

[pbaldy]

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.

 

[Minty]

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.