使用VBA将所有MS访问SQL查询导出到文本文件

I have to document an MS Access database with many many macros queries, etc. I wish to use code to extract each SQL query to a file which is named the same as the query, eg if a query is named q_warehouse_issues then i wish to extract the SQL to a file named q_warehouse_issues.sql

我必须文档与很多宏MS Access数据库查询,等等。我希望使用代码提取每个SQL查询文件命名的查询,例如如果一个名叫q_warehouse_issues查询我想提取SQL来一个名为q_warehouse_issues.sql的文件

I DO NOT WISH TO EXPORT THE QUERY RESULT SET, JUST THE SQL!

我不希望导出查询结果集，只希望导出SQL!

I know I can do this manually in Access, but i am tired of all the clicking, doing saveas etc.

我知道我可以在Access中手动执行这个操作，但是我厌倦了所有的点击、保存等等。

4 个解决方案

#1

This should get you started:

这应该让你开始:

  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef

  Set db = CurrentDB()
  For Each qdf In db.QueryDefs
    Debug.Print qdf.SQL
  Next qdf
  Set qdf = Nothing
  Set db = Nothing

You can use the File System Object or the built-in VBA File I/O features to write the SQL out to a file. I assume you were asking more about how to get the SQL than you were about how to write out the file, but if you need that, say so in a comment and I'll edit the post (or someone will post their own answer with instructions for that).

您可以使用文件系统对象或内置的VBA文件I/O特性将SQL写入文件。我猜你问的更多的是如何获得SQL，而不是如何写出这个文件，但是如果你需要的话，在评论中说出来，我会编辑这个帖子(或者有人会发布他们自己的答案并给出相应的说明)。

#2

Hope this helps.

希望这个有帮助。

Public Function query_print()
Dim db As Database
Dim qr As QueryDef

Set db = CurrentDb

For Each qr In db.QueryDefs
  TextOut (qr.Name)
  TextOut (qr.SQL)
  TextOut (String(100, "-"))
Next
End Function

Public Sub TextOut(OutputString As String)

    Dim fh As Long

    fh = FreeFile
    Open "c:\File.txt" For Append As fh
    Print #fh, OutputString
    Close fh

End Sub

#3

This solution include fields in query

这个解决方案包括查询中的字段

Public Sub ListQueries()
    ' Author:                     Date:               Contact:
    ' André Bernardes             09/09/2010 08:45    bernardess@gmail.com     http://al-bernardes.sites.uol.com.br/
    ' Lista todas as queries da aplicação.
    ' Listening:

    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim l As Integer

    On Error Resume Next

    For i = 0 To CurrentDb.QueryDefs.Count - 1
        Debug.Print "Query: " & CurrentDb.QueryDefs(i).Name

        For j = 0 To CurrentDb.QueryDefs(i).Fields.Count - 1
            Debug.Print "Field " & CurrentDb.QueryDefs(i).Fields(j).Name
        Next

        Debug.Print "  SQL: " & CurrentDb.QueryDefs(i).SQL
    Next
End Sub

#4

In the VB Window, click Tools->References....
在VB窗口中,点击工具- >引用....
In the References window add the dependency Microsoft Scripting Runtime by checking it off.
在“引用”窗口中，通过勾选这个依赖项来添加Microsoft脚本运行时。

Then this code will export the queries to a file suitable for using grep on:

然后该代码将查询导出到适合使用grep的文件:

Sub ExportQueries()

  Dim fso As New FileSystemObject

  Dim stream As TextStream

  Set stream = fso.CreateTextFile("e:\temp\queries.txt")

  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef

  Set db = CurrentDb()
  For Each qdf In db.QueryDefs

    stream.writeline "Name: " & qdf.Name
    stream.writeline qdf.SQL
    stream.writeline "--------------------------"
  Next qdf
  Set qdf = Nothing
  Set db = Nothing

End Sub

#1