Converting nested Access queries into raw nested SQL queries (1 Viewer)

Public Function ProcessQuery(QueryName As String, Optional NumberLevels As Long = 1, Optional QueryPrefix As String = "qry_")
    ProcessQuery = ProcessSQL(CurrentDb.QueryDefs(QueryName).SQL, NumberLevels)
End Function

Public Function ProcessSQL(TheSQL As String, Optional NumberLevels As Long = 1, Optional QueryPrefix As String = "qry_") As String
' NumberLevels is the number of nesting levels
' For this code to work, all queries must follow a standard naming convention, for example, starting with "qry_"

Dim FromPosition As Long
Dim LookForQueryNameStartHere As Long
Dim QueriesAlreadyDone As String

Dim QryNameStart As Long
Dim QryName As String
Dim QryNameLen As Long
Dim EndOfQueryName As Boolean

Dim RevisedSQLQueryOnwards As String
Dim ProcessedSQL As String
Dim i As Long
Dim j As Long
Dim CurrentCharacter As String


FromPosition = 0
QueriesAlreadyDone = ""

For i = 1 To NumberLevels
    ' start looking in the "from" clause . If in level 2, look for the next "from" etc
    FromPosition = InStr(FromPosition + 1, TheSQL, "from")
    LookForQueryNameStartHere = FromPosition
    
    Do
    ' find a query name using the query prefix eg "qry_".
        QryNameStart = InStr(LookForQueryNameStartHere, TheSQL, QueryPrefix)
        
        If QryNameStart = 0 Then
            ProcessSQL = TheSQL
            ' no more queries in this nesting level
            Exit For
        End If
        
        EndOfQueryName = False
        j = 0
        
        Do While EndOfQueryName = False
            j = j + 1
            CurrentCharacter = Mid(TheSQL, QryNameStart + j, 1)
            Select Case CurrentCharacter
                Case " ", ".", Chr(10), Chr(59), Chr(13) ' the end of the query name can be a space, dot, return character etc
                    EndOfQueryName = True
            End Select
        Loop
        
        QryNameLen = j
        
        QryName = Mid(TheSQL, QryNameStart, QryNameLen)
        
        If Not (QueriesAlreadyDone Like "*" & QryName & "*") Then ' process this query if it has not been processed before
            RevisedSQLQueryOnwards = Replace(TheSQL, QryName, ReplaceSQL(QryName), LookForQueryNameStartHere, 1)
            TheSQL = Left(TheSQL, LookForQueryNameStartHere - 1) & RevisedSQLQueryOnwards
            QueriesAlreadyDone = QueriesAlreadyDone & ", " & QryName ' add the processed query to the list of queries already processed
        End If
        
        ' see if any more queries in this level
        LookForQueryNameStartHere = QryNameStart + 1
    Loop
' go to next nesting level
Next i

ProcessSQL = TheSQL
End Function

Public Function ReplaceSQL(QueryName As String) As String
Dim TheSQL As String
TheSQL = CurrentDb.QueryDefs(QueryName).SQL
TheSQL = Mid(TheSQL, 1, Len(TheSQL) - 3)
    ReplaceSQL = "(" & TheSQL & ") as " & QueryName
End Function