MS Access - Creating a report based on a pass-through query that requires a parameter

Question

I am attempting to make a report in Access that uses parameters as well as a pass-through query.

I'm open to using a temporary table but the guides I can find using temporary tables all use an Insert query which doesn't work with my pass-through query.

I see a lot of mention of creating a recordset from the pass-though query and then copying the recordset to a temporary table but have not had luck location instructions on how to do that.

Some background. I have a PostgreSQL server that is aggregating multiple databases via a foreign data wrapper. Across those databases I have multiple table names that a reused.

I can do a normal pass-through query using the front end query properties but this doesn't handle parameters for the pass-through query.

I can build a form using ADO objects by setting the record set for the form to equal a record set created from a pass-through query. (Set Me.Recordset = MyRecordset) This allows me to create the query string dynamically so I can use parameters! One step closer but not a report.

Reports do not support setting themselves to a record set the way forms do so I'm back to the drawing board.

Any suggestion on how to accomplish my goal? Should I give up and have Postgres store the results in a table, link that table, and call it a day?

Answer 1

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 qryInCode_SQL_uspProject_Search.

EXEC dbo.uspProject_Search

@Active =
-- R01_BEG
-1
-- R01_END
, @Search =
-- R02_BEG
''
-- R02_END
-- R03_BEG
-- R03_END
-- R04_BEG
-- R04_END
-- R05_BEG
-- R05_END

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
        Next x
    End If

Exit_PROC:
    Set qdf = Nothing
    db.Close
    Set db = Nothing
    Exit Sub

Err_PROC:
    Select Case Err.Number
        Case Else
            ErrMsg = "Error: " & Err.Number & vbCrLf
            ErrMsg = ErrMsg & "Line: " & Erl() & vbCrLf
            ErrMsg = ErrMsg & Err.Description
            DoCmd.Hourglass False
            MsgBox ErrMsg, vbOKOnly + vbCritical, "Sub sitsUpdateSQLParams"
            Resume Exit_PROC
            Resume
    End Select

End Sub

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 @Search parameter.

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 qryReport_SQL_PT_01

Now, create a regular Microsoft Access SELECT query that simply does:

SELECT * FROM qryReport_SQL_PT_01 and call it qryReport_01

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.