How do you export MS Access Query Objects to text file

Question

In MS Access I need to back up all queries to a text file

Im able to do this with other Access objects fine, for example the following is a sample that backs up all reports to a text file

Dim oApplication
Set oApplication = CreateObject("Access.Application")

For Each myObj In oApplication.CurrentProject.AllReports
        WScript.Echo "Report  " & myObj.fullname
        oApplication.SaveAsText acReport, myObj.fullname, sExportpath & "\" & myObj.fullname & ".report"
Next

Ive tried the following to backup all queries

For Each myObj In oApplication.CurrentData.AllQueries
    WScript.Echo "Query  " & myObj.fullname
    oApplication.SaveAsText acQuery, myObj.Name, sExportpath & "\" & myObj.Name & ".query"
Next

However the resulting text file is the query output. Its definitely not the Query Definition that Im looking for.

To be clear here is an image of what Im trying to export to text

Does anyone have any ideas on how that can be accomplished?

Answer 1

Iterating through the QueryDefs should work for you

Dim def As DAO.QueryDef
Dim defCol As DAO.QueryDefs

Set defCol = CurrentDb.QueryDefs

For Each def In defCol
    Debug.Print def.SQL
Next

Answer 2

How about this (requires 'Microsoft Scripting Runtime' checked under Tools|References in the VBA editor):

Dim Def As DAO.QueryDef
Def FSO As New Scripting.FileSystemObject, Stream As Scripting.TextStream
For Each Def In CurrentDb.QueryDefs
  Set Stream = FSO.CreateTextFile(sExportpath & "\" & Def.Name & ".query")
  Stream.Write(Def.SQL)
  Stream.Close
Next

Alternatively, if you're using VBScript:

Dim Def, FSO, Stream
Set FSO = CreateObject("Scripting.FileSystemObject")
For Each Def In oApplication.CurrentDb.QueryDefs
  Set Stream = FSO.CreateTextFile(sExportpath & "\" & Def.Name & ".query")
  Stream.Write(Def.SQL)
  Stream.Close
Next