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?

Drew

I don't get that problem with Access 2007 or 2010. Using SaveAsText acQuery gives me the query definition ... which includes the SQL property and sundry other attributes Access stores in the definition. But SaveAsText definitely does not give me the data which running the query returns. Seems an odd "feature" to add to 2013. :-( – Maudemaudie

what value do you use for acQuery? Im setting acForm = 2, acModule = 5, acMacro = 4, acReport = 3, and acQuery = 6 – Drew

"which includes the SQL property and sundry other attributes Access stores in the definition" maby im not returning the SQL property in plain text and it has other data associated? (The returned text file was actually a very large binary encrypted file that I made the assumption as being the query result) – Drew

I did it from Access VBA. acQuery is 1. See AcObjectType Enumeration. None of those constants has 6 as its value. – Maudemaudie

great Ive been looking for that Enumeration sheet – Drew

Your answer was the correct one, if you place it as an answer ill accept it. Just make sure to mention that my acQuery constant was off so other people who read this have some context – Drew

Use the Object Browser in the Access VB Editor's IDE. I just searched for acQuery there. Then looked up the enumeration's name to find you that web link. – Maudemaudie

Answer 1

The value of the acQuery constant is 1. (AcObjectType Enumeration)

Perhaps your results are because the code is using 6 instead of 1. I don't know what should happen in that situation because none of the AcObjectType constants have a value of 6. Using Access VBA, when I tried SaveAsText with 6, something strange happened: the output file was created but Windows denied me permission to view its contents; shortly later, a dialog box appeared which looked like Access was looking for something on SQL Server ... although the query definition I was saving does not involve SQL Server. Strange stuff!

Maudemaudie

I found your post trying to figure out what 6 means. I found it in a utility add-in with little commenting. It appears to create a copy of the database file that includes the tables. It shouldn't surprise us that an undocumented command accepts some undocumented parameters. – Seclusive

Answer 2

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

Encrimson

Answer 3

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

Bikales

what do you mean "requires 'Microsoft Scripting Runtime' checked under Tools|References in the VBA" Im actually running this script in shell using cscript Its based on this answer: #188006 – Drew