[AccessD] dynamic sql - changing intermediate query sql on the fly at top level query runtime

I just can't seem to finesse this, thought maybe someone else could think of
a way. I wrote some code which examines a crosstab query and updates a
couple more queries which are dependent on that one. I have to do this
because the data which the crosstab pivots changes over time and all the
queries that depend on the crosstab become obsolete. The code is a little
cumbersome, but works. What I am looking for is some way to call that
function's code prior to my current top level query, but do it from a new
top level query. I think I will have to use a macro or VBA ... but I was
wondering if there might just be some clever construction in a new top level
query,  that calls the function updating its dependent queries, and uses '*'
to pull all records from the previous top level query ... without it having
to know ahead of time what those columns were going to be.

I tried this

   SELECT *
   FROM (SELECT *
   FROM OldTopLevelQuery)
   WHERE (((BuildAndRunEquipmentQueries())=True));

I thought great, since I am not listing any columns and since I have a WHERE
clause that calls the function, I would be home free.

But somehow Access still goes behind the scenes, creates a query plan with
whatever it knew about OldTopLevelQuery at the time this query got built ...
then if any of those columns disappear, the query fails. Even though it is
just asking for '*'. Go figure. So is there something else that I can do?

Here is the code, I don't think anyone needs to examine it to answer the
above question Y/N. Please remove code in your replies to avoid message
length limitations.

Thanks!



Function BuildAndRunEquipmentQueries() As Boolean
Const CROSSTAB = "qry_4444_Part2_Crosstab"
Const QUERYTOFIX1 = "qry_4444_Part3"
Const QUERYTOFIX2 = "qry_4444_Final"
Dim sFirstFields As String
Dim sTempName As String
Dim SQL As String
Dim D As DAO.Database
Dim R As DAO.Recordset
Dim ArrFields() As String
Dim Q As DAO.QueryDef
Dim iLB As Long
Dim iUB As Long
Dim SQLTest As String
Dim bFound As Boolean
Dim i As Long, j As Long
Dim sGrandTotal As String
Dim sFields As String
Set D = CurrentDb

'putting TempTextToBeSwapped as the table alias because we will change that
to 'A' later, and
'we won't be able to check elements coming back from 
           'FieldListFromQuerySQL(CROSSTAB)
'If we leave the "tablename." in the field list ... thereore we give
something unique 
'so using Replace$ before testing the field won't screw up  the field names

sFirstFields = ""
sFirstFields = sFirstFields & "," & "TempTextToBeSwapped.[GLOBAL_DB]"
sFirstFields = sFirstFields & "," &
"TempTextToBeSwapped.[Global_Customer_Name]"
sFirstFields = sFirstFields & "," & "TempTextToBeSwapped.[Regional_DB]"
sFirstFields = sFirstFields & "," &
"TempTextToBeSwapped.[Regional_Customer_Name]"
sFirstFields = sFirstFields & "," & "TempTextToBeSwapped.[SITE_DB]"
sFirstFields = sFirstFields & "," &
"TempTextToBeSwapped.[Site_Customer_Name]"
sFirstFields = sFirstFields & "," &
"TempTextToBeSwapped.[Site_Station_Name]"
sFirstFields = Mid(sFirstFields, 2)

ArrFields = FieldListFromQuerySQL(CROSSTAB)
iUB = UBound(ArrFields)
iLB = LBound(ArrFields)
sFields = ""
sGrandTotal = ""
For i = iLB To iUB
  bFound = False
  If InStr(UCase(Replace(sFirstFields, "TempTextToBeSwapped.", "")),
UCase(ArrFields(i))) = 0 Then
    sFields = sFields & "," & "A." & ArrFields(i)
    sGrandTotal = sGrandTotal & "+ NZ(" & "A." & ArrFields(i) & ",0)"
  End If
Next
sFirstFields = Replace$(sFirstFields, "TempTextToBeSwapped.", "A.")
If sFields <> "" Then
  sFields = Trim(Mid(sFields, 2))
  sGrandTotal = Trim(Mid(sGrandTotal, 2))

'QUERYTOFIX1
  SQL = "SELECT "
  SQL = SQL & sFirstFields & "," & sFields & ","
  SQL = SQL & sGrandTotal & " As Grand_Total"
  SQL = SQL & " FROM"
  SQL = SQL & " [" & CROSSTAB & "] as A"
  SQL = SQL & " ORDER BY "
  SQL = SQL & sGrandTotal & " DESC"
  
  SQLTest = "Select * from (" & SQL & ") WHERE 1 = 2"
  
  Set R = Nothing
  On Error Resume Next
  Set R = D.OpenRecordset(SQLTest)
  If Not R Is Nothing Then
    'SQL will work
    Set Q = Nothing
    sTempName = "qry_temp_" & Format(Timer + 1, "00000000")
    Set Q = D.CreateQueryDef(sTempName, SQL)
    D.QueryDefs.Refresh
    If Not Q Is Nothing Then
      Set Q = Nothing
      Set Q = D.QueryDefs(QUERYTOFIX1)
      If Q Is Nothing Then
        Set Q = D.CreateQueryDef(QUERYTOFIX1, SQL)
        D.QueryDefs.Refresh
      Else
        Q.SQL = SQL
      End If
    Else
      MsgBox "Unable to modify the query " & QUERYTOFIX1
    End If
  Else
    MsgBox "Unable to modify the query " & QUERYTOFIX1
  End If
  
'QUERYTOFIX2

  SQL = "SELECT "
  SQL = SQL & sFirstFields & "," & sFields & ","
  SQL = SQL & " A.Grand_Total"
  SQL = SQL & " FROM "
  SQL = SQL & " [qry_4444_Part3] as A"
  SQL = SQL & " INNER JOIN"
  SQL = SQL & " [qry_4444_Part4] as B"
  SQL = SQL & " ON"
  SQL = SQL & " A.GLOBAL_DB = B.GLOBAL_DB"
  SQL = SQL & " ORDER BY "
  SQL = SQL & " B.SumOfGrand_Total DESC, A.GLOBAL_DB, A.Grand_Total DESC"
  SQLTest = "Select * from (" & SQL & ") WHERE 1 = 2"
  Set R = Nothing
  On Error Resume Next
  Set R = D.OpenRecordset(SQLTest)
  If Not R Is Nothing Then
    'SQL will work
    Set Q = Nothing
    sTempName = "qry_temp_" & Format(Timer + 2, "00000000")
    Set Q = D.CreateQueryDef(sTempName, SQL)
    D.QueryDefs.Refresh
    If Not Q Is Nothing Then
      Set Q = Nothing
      Set Q = D.QueryDefs(QUERYTOFIX2)
      If Q Is Nothing Then
        Set Q = D.CreateQueryDef(QUERYTOFIX2, SQL)
        D.QueryDefs.Refresh
      Else
        Q.SQL = SQL
      End If
      BuildAndRunEquipmentQueries = True
    Else
      MsgBox "Unable to modify the query " & QUERYTOFIX2
    End If
  Else
    MsgBox "Unable to modify the query " & QUERYTOFIX2
  End If

Else
  MsgBox "Unable to properly test [" & CROSSTAB & "] nor update its
successor queries."
End If

End Function



Function FieldListFromQuerySQL(sQryName As String) As Variant
Dim D As DAO.Database
Dim R As DAO.Recordset
Dim F As DAO.Field
Dim Arr() As String
Dim sList As String

Set D = CurrentDb
Set R = D.OpenRecordset("Select * from [" & sQryName & "] where 1 = 2")
For Each F In R.Fields
  sList = sList & "|[" & F.Name & "]"
Next
If sList <> "" Then
  sList = Mid(sList, 2)
  Arr = Split(sList, "|")
End If
FieldListFromQuerySQL = Arr
End Function