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

William,

If the reason that the columns are changing when you run the crosstab query is that sometimes there is no data for some columns, then you can fix that by defining the crosstab such that all columns are always returned, with or without data. You can do that in query design mode by entering a comma delimited list of the desired filed named in the 'Column Headings' property of the query. The filed names should be within double quotes.

What that does to the SQL is modify the PIVOT clause so that it looks like ....

...PIVOT SomeTableOrQuery.SomeFiledName In ("Foo","Bar","FooBar"); 

So once your crosstab always returns the same columns there should be no need to modify the dependant queries.

HATH

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson (VBACreations.Com)
Sent: Friday, August 05, 2011 12:05 AM
To: 'Access Developers discussion and problem solving'
Subject: [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


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com