Pass-through query losing changes made to SQL

Question

I have a pass-through query MyPassThru. This is a simplified version of the query's SQL:

SET NOCOUNT ON

DECLARE @FromDate datetime = '1/25/2014'
DECLARE @ThruDate datetime = '3/1/2014'

SELECT *
  FROM MtgDailyTrans M
  WHERE M.TransDate >= @FromDate
    AND M.TransDate <= @ThruDate

I need to make changes to the @FromDate and @ThruDate parameters. I wrote the following function to do this (source for RegExReplace):

Private gDb As DAO.Database

Function Db() As DAO.Database
    If gDb Is Nothing Then Set gDb = CurrentDb
    Set Db = gDb
End Function

Sub MyPassThruSetDates(FromDate As Date, ThruDate As Date)
    Const FromPattern As String = "(@FromDate datetime = ')([\d/]+)'"
    Const ThruPattern As String = "(@ThruDate datetime = ')([\d/]+)'"
    Dim qd As DAO.QueryDef
    Set qd = Db.QueryDefs("MyPassThru")
    qd.SQL = RegExReplace(FromPattern, qd.SQL, "$1" & Format(FromDate, "m/d/yyyy") & "'")
    qd.SQL = RegExReplace(ThruPattern, qd.SQL, "$1" & Format(ThruDate, "m/d/yyyy") & "'")
    Set qd = Nothing
End Sub

The problem is that when I make changes to the SQL for MyPassThru and then run the MyPassThruSetDates() procedure, the changes I made to the SQL get rolled back. Why?

score 2 · Answer 1 · answered Apr 08 '14 at 17:20

The reason this is happening is that the persistent Db object is caching a copy of the QueryDef and its properties (to include the .SQL property). If you call MyPassThruSetDates, then make a change to the SQL for MyPassThru, then call MyPassThruSetDates again, the original SQL overwrites any changes made since the original call to MyPassThruDates.

The solution is to refresh the QueryDefs collection to ensure it is using the most current values:

Sub MyPassThruSetDates(FromDate As Date, ThruDate As Date)
    Const FromPattern As String = "(@FromDate datetime = ')([\d/]+)'"
    Const ThruPattern As String = "(@ThruDate datetime = ')([\d/]+)'"
    Dim qd As DAO.QueryDef
    Db.QueryDefs.Refresh   ' <--- This is the key!!!
    Set qd = Db.QueryDefs("MyPassThru")
    qd.SQL = RegExReplace(FromPattern, qd.SQL, "$1" & Format(FromDate, "m/d/yyyy") & "'")
    qd.SQL = RegExReplace(ThruPattern, qd.SQL, "$1" & Format(ThruDate, "m/d/yyyy") & "'")
    Set qd = Nothing
End Sub

For further explanation of why this happens, refer to the following heavily-commented test routine:

Sub TestDbCache()
    Const QName As String = "TempQry"

    Dim qd As DAO.QueryDef, db As DAO.Database

    'First, we create a querydef
    Set db = CurrentDb
    Set qd = db.CreateQueryDef(QName, "SELECT 'original'")
    Debug.Print qd.SQL                           '--> SELECT 'original';

    'Next, we update the querydef's .SQL outside the scope of our db object
    CurrentDb.QueryDefs(QName).SQL = "SELECT 'changed'"

    'The querydef and db objects are unaware of the change to .SQL
    Debug.Print qd.SQL                           '--> SELECT 'original';
    Debug.Print db.QueryDefs(QName).SQL          '--> SELECT 'original';
    Debug.Print CurrentDb.QueryDefs(QName).SQL   '--> SELECT 'changed';

    'Refreshing the collection updates both the db and qd objects
    db.QueryDefs.Refresh
    Debug.Print qd.SQL                           '--> SELECT 'changed';
    Debug.Print db.QueryDefs(QName).SQL          '--> SELECT 'changed';

    'Note that the .SQL is "SELECT 'changed'" when we set the NewDb object
    Dim NewDb As DAO.Database
    Set NewDb = CurrentDb

    'We change the .SQL without refreshing the NewDb's QueryDefs collection
    CurrentDb.QueryDefs(QName).SQL = "SELECT 'changed again'"

    'Since the NewDb object never cached the contents of the query,
    '   it returns the correct current value of .SQL
    Debug.Print NewDb.QueryDefs(QName).SQL       '--> SELECT 'changed again';

    'The other db object has not refreshed its QueryDefs collection,
    '   so it is wrong once again
    Debug.Print qd.SQL                           '--> SELECT 'changed';
    Debug.Print db.QueryDefs(QName).SQL          '--> SELECT 'changed';
End Sub

Pass-through query losing changes made to SQL

1 Answers1