'**************************************
' Name: Extract Query Text from MsAcces
' Description:This piece of code will ex
'     tract the sql statement embedded within 
'     and MS-Access Query, If you have any oth
'     er solutions pls let me know, else if fo
'     und useful pls don’t forget to vot
'     e
If any bugs/ suggestions found pls inform me too
so that I can Do the same In my library (As there are many Type of queries, I will say this is Not 100%)
' By: Joseph Varghese
'
' Inputs:Name of the query
User must have permission on “MSysQueries” and “MSysObjects” Else grant permission To Admin and other user from Tools-> Security-> User and Group Permissions
'
' Returns:SQL text of the query (not the
'     results), SQL Statements
'
' Assumes:Used to view the SQL syntax in
'     side a Ms-Access query
'
'This code is copyrighted and has' limited warranties.Please see http://w
'     ww.Planet-Source-Code.com/vb/scripts/Sho
'     wCode.asp?txtCodeId=70936&lngWId=1'for details.'**************************************

'Author: Joseph Varghese
'Email: josephvarghese78@hotmail.com
'If any bugs/ suggestions found pls info
'     rm me too
'so that I can do the same in my library
'     


Private Function export_access_query_text(sDBName As String, sQueryName As String) As String

    Dim objDB As New DBSession
    Dim sSQL As String
    Dim sFieldList As String
    Dim sWhereCond As String
    Dim sFromList As String
    Dim sGroupBy As String
    Dim sOrderBy As String
    Dim sTransform As String
    Dim sPivot As String
    Dim iFlag As Integer
    Dim bTransform As Boolean
    
    iFlag = 0
    objDB.Connect MsAccess, sDBName
    objDB.ExecuteQuery "SELECT Q.ATTRIBUTE, Q.EXPRESSION, Q.FLAG, Q.LVEXTRA, Q.NAME1, " & _
    "Q.NAME2, O.NAME AS [OBJECT NAME], Q.ORDER FROM MSYSOBJECTS AS O, MSYSQUERIES AS Q " & _
    "WHERE O.ID=Q.OBJECTID AND O.NAME='" & sQueryName & "'"
    
    


    While objDB.dbRecordSet.EOF = False


        Select Case objDB.dbRecordSet("Attribute")
            Case 1: bTransform = True: iFlag = 2
            
            Case 6:


            If bTransform = True Then
                sTransform = "TRANSFORM " & objDB.dbRecordSet("Expression") & IIf(Len(objDB.dbRecordSet("Name1")) > 0, " AS [" & objDB.dbRecordSet("Name1") & "] ", " ")
                bTransform = False
            Else


                If objDB.dbRecordSet("Flag") = iFlag Then
                    sFieldList = sFieldList & objDB.dbRecordSet("Expression") & IIf(Len(objDB.dbRecordSet("Name1")) > 0, " AS [" & objDB.dbRecordSet("Name1") & "], ", ", ")
                End If

            End If

            
            Case 5: sFromList = sFromList & objDB.dbRecordSet("Name1") & IIf(Len(objDB.dbRecordSet("Name2")) > 0, " " & objDB.dbRecordSet("Name2") & ", ", " ")
            
            Case 8: sWhereCond = objDB.dbRecordSet("Expression")
            
            Case 9:


            If objDB.dbRecordSet("Flag") = iFlag Then
                sGroupBy = sGroupBy & objDB.dbRecordSet("Expression") & ", "
            Else
                sPivot = " PIVOT " & objDB.dbRecordSet("Expression")
            End If

            
            Case 11: sOrderBy = sOrderBy & objDB.dbRecordSet("Expression") & IIf(objDB.dbRecordSet("Name1") = "d", " DESC, ", ", ")
        End Select

    objDB.dbRecordSet.MoveNext
Wend


If Len(sFieldList) > 0 And Right(Trim(sFieldList), 1) = "," Then sFieldList = Trim(Left(Trim(sFieldList), Len(Trim(sFieldList)) - 1))
If Len(sFromList) > 0 And Right(Trim(sFromList), 1) = "," Then sFromList = Trim(Left(Trim(sFromList), Len(Trim(sFromList)) - 1))
If Len(sGroupBy) > 0 And Right(Trim(sGroupBy), 1) = "," Then sGroupBy = Trim(Left(Trim(sGroupBy), Len(Trim(sGroupBy)) - 1))
If Len(sOrderBy) > 0 And Right(Trim(sOrderBy), 1) = "," Then sOrderBy = Trim(Left(Trim(sOrderBy), Len(Trim(sOrderBy)) - 1))

sSQL = sTransform & "SELECT " & Trim(sFieldList) & " FROM " & Trim(sFromList) & IIf(Len(sWhereCond) > 0, " WHERE " & Trim(sWhereCond), "") & IIf(Len(sGroupBy) > 0, " GROUP BY " & Trim(sGroupBy), "") & IIf(Len(sOrderBy) > 0, " ORDER BY " & Trim(sOrderBy), "") & sPivot

export_access_query_text = sSQL

objDB.DisConnect
Set objDB = Nothing
End Function

'usage
'txtSQLText = export_access_query_text("
'     D:\db.mdb", "MS")