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

enter image description here

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

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. :-( — HansUp, Nov 27 '13 at 20:59
what value do you use for acQuery? Im setting acForm = 2, acModule = 5, acMacro = 4, acReport = 3, and acQuery = 6 — MichaelTaylor3D, Nov 27 '13 at 21:08
"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) — MichaelTaylor3D, Nov 27 '13 at 21:10
I did it from Access VBA. `acQuery` is 1. See [AcObjectType Enumeration](http://msdn.microsoft.com/en-us/library/office/ff845495.aspx). None of those constants has 6 as its value. — HansUp, Nov 27 '13 at 21:22
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 — MichaelTaylor3D, Nov 27 '13 at 21:26
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. — HansUp, Nov 27 '13 at 21:26

score 6 · Answer 1 · answered Nov 27 '13 at 20:49

6

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

answered Nov 27 '13 at 20:49

Steve Salowitz

1,283
1
14
28

score 5 · Answer 2 · edited Apr 28 '15 at 17:01

5

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

edited Apr 28 '15 at 17:01

Kristen Waite

1,385
2
15
28

answered Nov 27 '13 at 20:58

Chris Rolliston

4,788
1
16
20

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: http://stackoverflow.com/questions/187506/how-do-you-use-version-control-with-access-development – MichaelTaylor3D Nov 27 '13 at 21:11

score 1 · Accepted Answer · answered Nov 27 '13 at 21:34

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!

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. — Ben, Jun 06 '19 at 08:20

How do you export MS Access Query Objects to text file

3 Answers3