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.

Shrum

Personally I'd just export the query names and document what they are used for. Rather than the complete SQL string. All documentation gets out of date rather rapidly in such situations as the folks working on the database have much better things to do than remember to save the query string each time they make changes. – Astto

This question and the solution are highly valuable as I prepare to re-engineer an Access application. The built-in Documentor has serious flaws and one of the worst is that SQL for queries is truncated. – Immunogenetics

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

Amandie

David, thanks very much. That is just what i needed (how to get the SQL). Should make life easy... – Shrum

Only just found out how to mark it. It was not obvious, but StackOverflow is great! – Shrum

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

Ratcliffe

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

Bickering

Please don't use a signature block. There's a link to your profile on your questions and answers which acts as your signature on SO. People can click on that to view your profile, and you can post whatever contact information you want there. – Wafd

Dude, you have no idea how much work you just saved me. Thanks! – Euphrates

It needs to be For i = 0 To CurrentDb.QueryDefs.Count - 1 not TableDefs – Headwaiter

Answer 4

1. In the VB Window, click Tools->References....
2. In the References window add the dependency Microsoft Scripting Runtime by checking it off.

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

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

Boffin

Answer 5

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.txt file 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.

VBA code to extract the SQL queries

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_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

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)

Selina

I suggest creating a new (self-answered) question for this, since the relevant new thing here is the Python and SQLite part. – Mountainside

Thanks @Andre, I posted a new self-answered question: How to convert MS Access queries to SQLite views? but according to another user it's not good enough. – Selina

Well, I can't comment on the Python part, but the question is good IMHO. And it seems to lead to new knowledge. :) – Mountainside

@Paul Rougieux link to the other question is broken – Viquelia

@Viquelia yes my self-answered question was closed: "This post is hidden. It was automatically deleted 9 months ago" [...] "Closed. This question needs to be more focused." It was the same answer as this one. The only interesting part was the comment thread about differences in SQL syntax. – Selina

The file should be closed at he end of the VBA-code. Without it I missed some parts, that were not flushed. Close #ff – Apportion