vba 如何将 MS Access 查询对象导出到文本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20252599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you export MS Access Query Objects to text file
提问by MichaelTaylor3D
In MS Access I need to back up all queries to a text file
在 MS Access 中，我需要将所有查询备份到文本文件
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
我可以用其他 Access 对象很好地做到这一点，例如以下是将所有报告备份到文本文件的示例
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?
有没有人对如何实现这一点有任何想法？
采纳答案by HansUp
The value of the acQueryconstant is 1. (AcObjectType Enumeration)
所述的值acQuery恒定为1。（AcObjectType枚举）
Perhaps your results are because the code is using 6 instead of 1. I don't know what shouldhappen in that situation because none of the AcObjectTypeconstants have a value of 6. Using Access VBA, when I tried SaveAsTextwith 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!
也许你的结果是因为该代码使用6而不是1。我不知道什么应该在这种情况下发生，因为没有任何的AcObjectType常量的使用访问VBA，当我尝试过的6值SaveAsText6，奇怪的事情发生：输出文件已创建，但 Windows 拒绝我查看其内容；不久之后，出现了一个对话框，看起来像是 Access 正在 SQL Server 上寻找某些东西……尽管我保存的查询定义不涉及 SQL Server。奇怪的东西！
回答by Steve Salowitz
Iterating through the QueryDefs should work for you
遍历 QueryDefs 应该对你有用
Dim def As DAO.QueryDef
Dim defCol As DAO.QueryDefs
Set defCol = CurrentDb.QueryDefs
For Each def In defCol
Debug.Print def.SQL
Next
回答by Chris Rolliston
How about this (requires 'Microsoft Scripting Runtime' checked under Tools|References in the VBA editor):
这个怎么样（需要在 VBA 编辑器的工具|参考下检查“Microsoft Scripting Runtime”）：
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:
或者，如果您使用的是 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
