Pass-Through Query

[Frank Martin]

I am trying to run storedprocedure from SQL via pass-through query. The
query contains parameter prompt in sql, but it does not work in Access
unless I actually write the parameters in Access query. Does anyone know
how I can get around this problem?

e.g. "exec StoredProcedure" should give me prompt [Type Business or Enter
for All] then followed by [Week No?]
at the moment only way for the query to work is by inserting responses in
query: "exec StoredProcedure Sales, 40"

Your advice would be much appreciated.

Frank

 

[Douglas J. Steele]

Sorry, but you can't use parameters in pass-through queries.

What you can do, though, prompt through VBA, and dynamically rewrite the SQL
for the query. Something like the following untested air-code should do it:


Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim strBusiness As String
Dim strSQL As String
Dim strWeek As String

strBusiness = InputBox("Type Business or Enter for All")
strWeek = InputBox("Week No?")

strSQL = "exec StoredProcedure "
If Len(strBusiness) > 0 Then
strSQL = strSQL & Chr(39) & strBusiness & Chr(39) & " "
End If
strSQL = strSQL & strWeek

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("MyPassthroughQuery")
qdfCurr.SQL = strSQL


However, if I'm reading your prompt appropriately, you want them to just hit
Enter to indicate all. If that's the case, you won't have anything to pass
as the first parameter, so you can't use positional parameters and should
use named parameters:

If Len(strBusiness) > 0 Then
strSQL = strSQL & "@Param1 = '" & strBusiness & "', "
End If
strSQL = strSQL & "@Param2 = " & strWeek

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("MyPassthroughQuery")
qdfCurr.SQL = strSQL