Documenting the queries in your database

If you have built an Access database of any size, it is likely
that you will have a large number of queries. If you want to
document the SQL for current and future users, you can use the
Database Documenter that comes with Access but you will be left with
a printed report, or an RTF file that requires a lot of cleaning up
if you want present the output in a different format.

This article show how to build a table that will hold all of your
query names and SQL. You can then sort, filter and search the table,
and you can use it as the basis for some useful reports. It’s also
good for peace of mind, because any time you accidentally change or
remove a query you can reinstate it by copying the SQL to a new
query and saving it with the original name.

Update: There is a
new article showing how to document the dependencies between
your queries. As a result, the table has an additional field (QueryType)
and the WriteSQLForQueries function has been updated to populate the
new field.

The Table

The table is called ztblQueries, and has 4 fields as described
below:

The Code

Create a new module called basDocumentation and place this
function in the new module.

Note: You must set a
reference to the Microsoft DAO 3.6 Object Library to run this
code.

'Requires a reference to the Microsoft DAO 3.6 Object Library
Function WriteSQLForQueries()
  Dim dbs As DAO.Database
  Dim qry As DAO.QueryDef

  DoCmd.SetWarnings False
  Set dbs = CurrentDb()

  'remove contents of table before re-populating
  DoCmd.RunSQL "DELETE * FROM ztblQueries"

  'loop through queries, writing names and SQL to the table
  For Each qry In dbs.QueryDefs
    If Left(qry.Name, 1) <> "~" Then
      DoCmd.RunSQL "INSERT INTO ztblQueries ( QueryName, QuerySQL, QueryType ) " _
        & "SELECT '" & qry.Name & "' AS qName, '" & qry.SQL & "' AS qSQL, " & qry.Type & " AS qType;"
    End If
  Next qry
  DoCmd.SetWarnings True

  'clean up references
  Set dbs = Nothing

End Function

When you are modifying data via SQL, Access will prompt you with
a warning that you are about to add / delete / modify xxx records.
When you are looping through many records as in this example,
responding to these prompts is a hassle that you can do without.
That is why the DoCmd.SetWarnings False line is
used. At the end of the code we reset it with
DoCmd.SetWarnings True so that you continue to get warnings
about changes while you are designing queries, forms and reports.

Before adding the query documentation to the table, we delete any
existing records to make sure that there is no duplication from
previous runs:

  DoCmd.RunSQL "DELETE * FROM ztblQueries"

The main work of the function is done in the loop. In this case
we are excluding SQL strings from combo and list boxes, forms and
reports, where the SQL has not been saved as a query. That is what
the If statement does: when you document unsaved SQL statements they
are prefixed with a tilde (~), so the If statement will exclude
them. If you want all of the SQL statements included, comment out
the If and End If lines.

The DoCmd.RunSQL statement inside the loop uses Append queries to
add the query information to the table.

If you are not appending data to all fields in the table you must
define which fields will be used, which is what you see in the
INSERT clause:

"INSERT INTO ztblQueries ( QueryName, QuerySQL, QueryType ) "

In the second, SELECT, clause we define what data is being added
to the fields: the name and SQL of the current query in the
QueryDefs collection. Because qry.Name and qry.SQL are text
variables, they need to be enclosed in quotes. When you want to use
a quote as a literal piece of text in a VBA string, you have two
options: double the quote (so that ” becomes “”) or use a
combination of single and double quotes, as in the code that I used
in this example. So, the SELECT clause could be written as either

"SELECT '" & qry.Name & "' AS qName, '" & qry.SQL & "' AS qSQL, " & qry.Type & " AS qType;"

or

"SELECT """ & qry.Name & """ AS qName, """ & qry.SQL & """ AS qSQL, " & qry.Type & " AS qType;"

My personal preference is to mix the quotes as in the first
example, but both will work. Note that because the data we are
adding is not from an existing field, we need to create an alias for
the SQL to work with.

Running the code

Create a new form and add a command button to the form. Create a
VBA procedure for the button’s Click event and insert this
one-liner:

basDocumentation.WriteSQLForQueries

If you are unsure how to create an event procedure, see
this tip. Save the
form, click the button, and the table will be populated with the
query names and SQL.

 
Home | About |
Links |

Contact | Excel |
Access