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
I DO NOT WISH TO EXPORT THE QUERY RESULT SET, JUST THE SQL!
I know I can do this manually in Access, but i am tired of all the clicking, doing saveas etc.
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).
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