vba - Updating an Access Query definition (SQL) and reflecting in dependent queries

Question

I am still relatively new to Access and the functionality, but bottom line is I have a query (called ScenarioQuery), which pulls data from a table ScenarioDetails and creates columns which are equivalent to fiscal periods, so each column will be a calculated column like

 IIf(MonthNumber = 1, [Value], 0) AS [S2020Jan]

What I am trying to do is use VBA to dynamically update the year as part of the column aliases as each "scenario" will contain a different year range.

I have successfully set up the macros to update the years dynamically through using arrays and recordsets, however, I have linked (dependent) queries which reference the field names in their own result sets and these don't update automatically when a query def is updated (at least as far as I know)

Is there a way to accomplish this dependency chain update? Or would I either have to write further macros to update these queries / alter the objects to pull from another source ?

标签： vbams-accessdependencies

Answer 1

像这样使用QueryDefs集合

For Each q In CurrentDb.QueryDefs
    q.SQL = Replace(q.SQL, "2020", "2021")
Next q

https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/querydefs-collection-dao