Using VBA to 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.QueryDef
    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

I would like to see the additional code to write to a file. Could you provide it?

Answer 3

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.TableDefs.Count - 1
        Debug.Print "Query: " & CurrentDb.QueryDefs(i).Name

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

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