Export all MS Access SQL queries to text files

Question

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.

Answer 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).

Answer 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