ptReplaceDeclaredValues(): A VBA Function to Update SQL in Pass-Through Queries

Pass-through queries are a great way to boost performance of an Access application with a SQL Server (or other ODBC-linked database) backend.

Using a pass-through query lets you:

• Off-load processing to the database server (often boosting performance)
• Pass less data from the server to the client (often boosting performance)
• Use proprietary SQL Server features (often boosting performance)
• Use more granular database security (often boosting...um...security)

The challenge with pass-through queries is that you cannot easily pass filtering criteria to a report or form that is based on a pass-through query and have that criteria applied at the server.

A Less-Than-Ideal Solution

One option is to dynamically create the entire SQL statement in VBA and then assign it to a saved pass-through query definition.  

This has a few drawbacks:

• Building strings in VBA often results in messy, hard-to-read code
• SQL Server could see each run of the pass-through as a new query and generate a new execution plan (often tanking performance)
• Version control diffs will make it look like the WHERE clause itself changed

There must be a better way.

A Better Approach

Instead of dynamically creating an entire T-SQL statement every time, we can declare values at the top of our T-SQL statement and use regular expressions to replace just the declared values.

This avoids having to build strings in VBA; it guarantees SQL Server will cache the execution plan; and version control diffs will see that the WHERE clause has not been changed.

The Algorithm

The first step happens in the design of the pass-through query's T-SQL.  We need to extract variables from the body of the T-SQL and set them in Declare statements in the T-SQL "header" area.

Instead of:

SELECT *
FROM MyTable
WHERE RptDate = '1980-01-01'

We would use:

DECLARE @RptDate datetime = '1980-01-01'

SELECT *
FROM MyTable
WHERE RptDate = @RptDate

With that in place, here's the algorithm we use in our VBA subroutine:

1. Loop through a parameter array of variable name - variable value pairs
    NOTE: Use Step 2 to loop through just the variable names
2. Use a regular expression to replace everything to the right of the equal sign with the associated value in the name-value parameter array
3. Update the query definition's SQL property with the replaced string

The Code

Dependencies

• RegExReplace() function

'---------------------------------------------------------------------------------------
' Procedure : ptReplaceDeclaredValues
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/ptreplacedeclaredvalues/
' Date      : 5/8/2015
' Purpose   : Replaces declared values in a pass-through query.
' Usage     : ptReplaceDeclaredValues "ptAct319Transfers", "@Start", "'1/1/2015'", "@End", "'12/31/2015'"
' Notes     - Each T-SQL parameter should be declared on a separate line
'           - Everything to the right of the equal sign will be replaced
'               Declare @Start datetime = '1/1/1980'
'               Declare @End datetime = '1/1/1980'
'           - Each passed value should be correctly formatted before being passed
'---------------------------------------------------------------------------------------
'
Private Sub ptReplaceDeclaredValues(QryName As String, _
                                    ParamArray NameValuePairs() As Variant)
    Dim qd As DAO.QueryDef
    Set qd = CurrentDb.QueryDefs(QryName)

    Dim i As Integer
    For i = LBound(NameValuePairs) To UBound(NameValuePairs) Step 2
        Dim NAME As String: NAME = NameValuePairs(i)
        Dim Value As String: Value = NameValuePairs(i + 1)
        Dim Pattern As String
        Pattern = "(Declare " & NAME & "[^=]*=)([^\r\n]*)"
        qd.SQL = RegExReplace(Pattern, qd.SQL, "$1" & Value)
    Next i
    Set qd = Nothing

End Sub

Sample Usage

The ptReplaceDeclaredValues subroutine never gets called on its own.

Instead, it's the first step before opening a form or report whose RecordSource depends on the contents of the associated pass-through query.  

There's a lot of information packed into that last sentence, and it might be a bit hard to follow.  Let's explore a practical example to help clarify things.

Naming Convention Explained

The pt prefix stands for "Pass-Through."  So the ptOwnerAddressChange routine gets called to update the T-SQL for the "OwnerAddressChange" pass-through query definition.

ptOwnerAddressChange() Subroutine:

The following subroutine gets called before opening a report whose RecordSource is the OwnerAddressChange query definition.  The Qs() function wraps the enclosed text in escaped single quotes.

I could call ptReplaceDeclaredValues directly from my preview report button click event handler. I prefer to create standalone convenience functions for each pass-through query with declared values that must be dynamically set via the ptReplaceDeclaredValues subroutine.  This help makes the code self-documenting, but more importantly, it lets me keep all of my related pass-through update functions in a dedicated code module.

Sub ptOwnerAddressChange(StartDate As Date, EndDate As Date)
    ptReplaceDeclaredValues "OwnerAddressChange", _
                            "@StartDate", Qs(Format(StartDate, "m/d/yyyy")), _
                            "@EndDate", Qs(Format(EndDate, "m/d/yyyy"))
End Sub

btnOwnerAddressChanges_Click() Event Handler:

The following code previews the Owner Address Change report.

In the first part of the routine, we use two guard clauses to make sure the user filled in the required start and end dates.

In the middle part of the routine, we update the SQL property of the OwnerAddressChange pass-through query to use the dates the user entered in the associated text boxes.

In the final part of the routine, we preview the report.  Notice that we do not pass any filtering criteria to the report.  The filtering was done in the middle part when we directly edited the query definition's SQL.

Private Sub btnOwnerAddressChanges_Click()
    'https://nolongerset.com/filling-in-the-blanks/
    If ControlIsBlank(Me.tbStartDate) Then Exit Sub
    If ControlIsBlank(Me.tbEndDate) Then Exit Sub
    
    'Update the start and end dates on the OwnerAddressChange pass-thru query
    ptOwnerAddressChange Me.tbStartDate, Me.tbEndDate
    
    'https://nolongerset.com/previewreport-function/
    PreviewReport "OwnerAddressChangeRpt"
End Sub

OwnerAddressChange Pass-Through SQL

Here's the associated T-SQL.  The important bits are lines 3 and 4:

SET NOCOUNT ON

Declare @StartDate datetime ='4/11/2016'
Declare @EndDate datetime ='4/11/2016'

SELECT P.PropertyID
 , CH.ChangeDate
 , P.ControlNumber
 , P.TownshipBorough
 , P.TaxMap
 , C.Name AS ClientName
 , CH.Before
 , CH.After
 , CH.Type
 , CH.FieldOrder
 , CH.FieldName
 , CH.ChangeTime
 , CH.ApprovalInitials
 , P.TownShipBorough
 , P.TaxMap
FROM (((Property AS P
  INNER JOIN Ownership AS Os ON P.PropertyID = Os.PropertyID)
  INNER JOIN Owner AS O ON Os.OwnershipID = O.OwnershipID)
  INNER JOIN Client AS C ON O.ClientID = C.ClientID)
  INNER JOIN ClientHistory AS CH ON C.ClientID = CH.ClientID
WHERE (((CH.ChangeDate) Between @StartDate And @EndDate)
  AND ((CH.Type)='C')
  AND ((CH.FieldOrder) In (2,3,4))
  AND ((Os.CurrentOwnership)<>0));

UPDATE [2023-06-02]: As Brenda Bachtold points out in the comments below, the first line SET NOCOUNT ON is required to avoid the error message, "Pass-through query with ReturnsRecords property set to True did not return any records."  Thanks for pointing that out, Brenda!

OwnerAddressChange.txt:

SaveAsText acQuery, "OwnerAddressChange", "OwnerAddressChange.txt"

Here are the contents of the OwnerAddressChange.txt file as generated via the Access VBA command above:

dbMemo "SQL" ="SET NOCOUNT ON\015\012\015\012Declare @StartDate datetime ='4/11/2016'\015\012De"
    "clare @EndDate datetime ='4/11/2016'\015\012\015\012SELECT P.PropertyID\015\012 "
    ", CH.ChangeDate\015\012 , P.ControlNumber\015\012 , P.TownshipBorough\015\012 , "
    "P.TaxMap\015\012 , C.Name AS ClientName\015\012 , CH.Before\015\012 , CH.After\015"
    "\012 , CH.Type\015\012 , CH.FieldOrder\015\012 , CH.FieldName\015\012 , CH.Chang"
    "eTime\015\012 , CH.ApprovalInitials\015\012 , P.TownShipBorough\015\012 , P.TaxM"
    "ap\015\012FROM (((Property AS P\015\012  INNER JOIN Ownership AS Os ON P.Propert"
    "yID = Os.PropertyID)\015\012  INNER JOIN Owner AS O ON Os.OwnershipID = O.Owners"
    "hipID)\015\012  INNER JOIN Client AS C ON O.ClientID = C.ClientID)\015\012  INNE"
    "R JOIN ClientHistory AS CH ON C.ClientID = CH.ClientID\015\012WHERE (((CH.Change"
    "Date) Between @StartDate And @EndDate)\015\012  AND ((CH.Type)='C')\015\012  AND"
    " ((CH.FieldOrder) In (2,3,4))\015\012  AND ((Os.CurrentOwnership)<>0));"
dbMemo "Connect" ="ODBC;Driver={ODBC Driver 17 for SQL Server};Server=db\\gb;Database=TaxDB;Trusted"
    "_Connection=Yes;"
dbBoolean "ReturnsRecords" ="-1"
dbInteger "ODBCTimeout" ="60"
Begin
End