Here is how I approach this kind of task. I use Microsoft Access with SQL Server back end all the time, and I either use parameters for stored procedures in pass-through queries, or dynamic sql in a pass-through queries all the time.
If you are calling a stored procedure in a pass-through query from Microsoft Access and you need to set the parameters, here is how I do it.
In your pass-through query, use special formatting to assist with dynamic search and replace, such as the following example. This is a pass-through query that I created in Microsoft Access. Note, that in a pass-through query, sql server (in my case) syntax applies, so I have put special comments in the query so that I can use code to search and replace the parameter values in between those comments. In this example, my stored procedure takes two parameters, and the name of this pass-through query is
, @Search =
Notice the careful placement of the parameter names and separaters (commas) so that only the parameter value appears in between the begin/end markers.
Now, I have written a subroutine to automate replacement (setting) of parameters in any pass-through query.
Public Sub sitsUpdateSQLParams(stQName As String, ParamArray Params() As Variant)
On Error GoTo Err_PROC
Dim ErrMsg As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim mssql As String
Dim x As Long
Dim y As Long
Set db = CurrentDb()
If stQName <> "" Then
Set qdf = db.QueryDefs(stQName)
For x = 0 To UBound(Params)
y = InStr(qdf.SQL, "-- R" & Format(x + 1, "00") & "_BEG")
mssql = Left(qdf.SQL, y + 9)
mssql = mssql & vbCrLf & Params(x) & vbCrLf
y = InStr(qdf.SQL, "-- R" & Format(x + 1, "00") & "_END")
mssql = mssql & Mid(qdf.SQL, y)
qdf.SQL = mssql
Set qdf = Nothing
Set db = Nothing
Select Case Err.Number
ErrMsg = "Error: " & Err.Number & vbCrLf
ErrMsg = ErrMsg & "Line: " & Erl() & vbCrLf
ErrMsg = ErrMsg & Err.Description
MsgBox ErrMsg, vbOKOnly + vbCritical, "Sub sitsUpdateSQLParams"
You will notice that this subroutine takes the name of the pass-through query as the first parameter, and then it take a
Variant parameter array for all subsequent parameters. This allows you to specify as many parameter values for replacement in the pass-through query as needed.
You would call this routine for my sample pass-through query as follows:
sitsUpdateSQLParams "qryInCode_SQL_uspProject_Search", 0, "'def'"
That will cause
0 to be passed for the
@Active parameter and
'def' to be passed for the
Also note, that your parameters in this function call must be listed on the above function call in the order they appear in your pass-through query. My code is relying on the first replacement parameter value being inserted into the
R01 block, and the second being inserted into the
R02 block, and so forth.
So, in your case, for a report, I would do the following:
Create a pass-through query and format it like my example, so that the parameter replacement routine can correctly replace those parameters.
For this example, lets call your Microsoft Access pass-through query
Now, create a regular Microsoft Access
SELECT query that simply does:
SELECT * FROM qryReport_SQL_PT_01 and call it
Now, your report should have
qryReport_01 as the RecordSource.
For your user interface, you now need to use a form, or some other method, to prompt the user for the parameter values so that you can have those values availble in VBA code. For this to work, you can no longer rely on Microsoft Access to auto-prompt the user for parameters you reference in this query. You must gather those from the user yourself.
Once you have those values, you can call the routine to update the pass-through query and then open the report. The report will now reflect the parameters you collected from the user.