36

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.

2
  • 1
    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. Commented Aug 15, 2009 at 1:51
  • 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. Commented Aug 2, 2023 at 13:50

5 Answers 5

38

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

Sign up to request clarification or add additional context in comments.

2 Comments

David, thanks very much. That is just what i needed (how to get the SQL). Should make life easy...
Only just found out how to mark it. It was not obvious, but StackOverflow is great!
23

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

Comments

12

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

3 Comments

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.
Dude, you have no idea how much work you just saved me. Thanks!
It needs to be For i = 0 To CurrentDb.QueryDefs.Count - 1 not TableDefs
6
  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

Comments

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)

7 Comments

I suggest creating a new (self-answered) question for this, since the relevant new thing here is the Python and SQLite part.
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.
Well, I can't comment on the Python part, but the question is good IMHO. And it seems to lead to new knowledge. :)
@jabellcu 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.
@PaulRougieux my man! You just saved me so much time. The sooner I can get Access out of my life, the better.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.