DataWright Information Services

Consulting and Resources for Excel and Access

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:

Field Data Type Comments
qryID AutoNumber Primary Key
QueryName Text (255)  
QuerySQL Memo  
QueryType Number  

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.