使用 VBA 将所有 MS Access SQL 查询导出到文本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1275502/
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
Using VBA to export all MS Access SQL queries to text files
提问by Pieter Nienaber
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 中手动执行此操作，但是我厌倦了所有的点击、保存等操作。
回答by David-W-Fenton
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，而不是关于如何写出文件，但如果您需要，请在评论中说出来，我会编辑帖子（或者有人会发布他们自己的答案）说明）。
回答by yoonjinl
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
回答by Andre Bernardes
This solution include fields in query
此解决方案包括查询中的字段
Public Sub ListQueries()
' Author: Date: Contact:
' André Bernardes 09/09/2010 08:45 [email protected] 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
回答by leeand00
- In the VB Window, click
Tools->References.... - In the References window add the dependency
Microsoft Scripting Runtimeby checking it off.
- 在 VB 窗口中，单击
Tools->References.... - 在 References 窗口中，
Microsoft Scripting Runtime通过选中它来添加依赖项。
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
回答by Paul Rougieux
I modified @andre-bernardes's code to use
"|" separators before the query names
and ":" separators before the SQL statements.
The different separators make it easier to parse the Queries.txtfile with python and create a dictionnary of queries and SQL statements.
You can then use this dictionary to create views in an SQLite table for example.
我修改了@andre-bernardes 的代码以使用“|” 查询名称前的分隔符和 SQL 语句前的“:”分隔符。不同的分隔符可以更轻松地Queries.txt使用 python解析文件并创建查询和 SQL 语句的字典。例如，您可以使用此字典在 SQLite 表中创建视图。
VBA code to extract the SQL queries
用于提取 SQL 查询的 VBA 代码
Public Sub ListQueries()
' Modified from André Bernardes
Dim i As Integer
Dim ff As Long
ff = FreeFile()
Open "C:\Dev\Queries.txt" For Output As #ff
On Error Resume Next
For i = 0 To CurrentDb.QueryDefs.Count - 1
Debug.Print "|" & CurrentDb.QueryDefs(i).Name & ":"
Print #ff, "|" & CurrentDb.QueryDefs(i).Name & ":"
Debug.Print CurrentDb.QueryDefs(i).SQL
Print #ff, CurrentDb.QueryDefs(i).SQL
Next
End Sub
Python code to parse Queries.txt into a dictionary
将 Queries.txt 解析为字典的 Python 代码
queries_file = open(data_path + '/Queries.txt')
queries = queries_file.read().split('|')
l = [x.split(':') for x in queries]
l.pop(0)
table_name_to_query = {name: query for name, query in l}
Create SQLite views from the Access queries
从 Access 查询创建 SQLite 视图
import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()
for table, query in table_name_to_query.items():
try:
c.execute("CREATE VIEW `%s` AS %s" % (table,query))
print("\n\n"+ table + " passed")
print(query)
except Exception as e:
print("\n\n"+ table + " error")
print(e)
print(query)
