DataWright Information Services

Consulting and Resources for Excel and Access

Documenting query dependencies

If you have to document the queries on your database you will often need to know:

  • What queries do I have in the database, and what is the SQL for those queries?
  • What queries and tables in the database lie upstream of any query; ie, what data does my query depend on?

In an earlier article I showed how to document query SQL. This article builds on that documentation to answer the second question.

Query dependencies -- built-in options

Access has a built-in option for viewing object dependencies, if you have version 2003 or higher. It requires you to turn on a feature called Name Auto-correct which has a reputation for causing problems with forms, queries, reports and general database performance, as well as contributing to database crashes (see link). The feature is enabled by default and the current recommendation is that you turn it off. If you do decide to use Name Autocorrect, be aware of the risks.

Viewing object dependencies in Access 2003

To view object dependencies in Access 2003, do the following:

  • Select an object (Table, Query, Form or Report) from the list on the left of the database container.
  • Select View | Object Dependencies. If the Name Autocorrect feature is turned off you will get a warning that the feature will be turned on, and that you may have a delay of several minutes. Click OK. You will see a new pane at the right of the database window, with all of the objects that depend on the one you selected. You can toggle the view to see objects on which your selected object depends.
  • If you select another object, go back to View | Object Dependencies and the pane will refresh.

Viewing object dependencies in Access 2007

  • Select an object from the list on the left of the database window.
  • In the Database Tools ribbon, select Object Dependencies from the Show/Hide group. You will get the same warning as from Access 2003 (above); click OK and the pane will appear at the right of the database window.
  • If you select another object, click Refresh on the pane to update the dependencies.

Turning off Name Autocorrect in Access 2000-2003

To turn off Name AutoCorrect do the following:

  • Go to Tools | Options | General
  • In the Name Autocorrect section (right hand side, halfway down), deselect the options

Turning off Name Autocorrect in Access 2007

In Access 2007, you turn off Name Autocorrect by doing the following:

  • Click the Office button at the top left of the screen, and then select the Access Options at the bottom of the dialog
  • Select the Current Database button, and scroll to the bottom of the form. Deselect any options in the Name Autocorrect section and click OK. You may be prompted to restart the database before your changes take effect.

Query dependencies -- do it yourself

To document your queries so that you can see the dependencies in any version from 2000 up, you can add another couple of functions and a new form to the original method.

First, you will need to modify the design of ztblQueries and create a new table called ztblReferencedQueries, which holds the relationship data. If you haven't used the code for documenting tables you will also need to create ztblTables and run the WriteTableList function below.

The modified query table: ztblQueries

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

The modified table has one additional field: a Number field called QueryType.

A table for the relationship data: ztblReferencedQueries

Field Data Type Comments
RefID AutoNumber Primary Key
QueryName Text (255)
RefName Text (255)
Relationship Text (50)

A table that lists the tables in the database: ztblTables

Field Data Type Comments
TableName Text (255)  

The code

The first routine writes a list of tables -- we'll use this for checking references to tables later on. Once existing records are deleted, the function loops through the TableDefs collection, adding the names of any tables that are not system, utility or temp tables. 

Function WriteTableList()
  ''=========================================================''
  ''Writes name of all tables
  ''to ztblTables. Table is cleared first.
  ''=========================================================''
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim tdf As DAO.TableDef
  Dim sSQL As String
  
  sSQL = "DELETE * FROM ztblTables"
  
  'Clear out ztblTables to avoid duplication
  DoCmd.SetWarnings False
  DoCmd.RunSQL sSQL
  DoCmd.SetWarnings True

  Set dbs = CurrentDb()
  Set rst = dbs.TableDefs("ztblTables").OpenRecordset

  'Add table names to ztblTables. The If statement excludes system tables (MSYS*), 
  'utility tables used in the documentation process (z*), and
  'temporary tables caused by deletion of tables in other routines (~*)
  For Each tdf In dbs.TableDefs 'exclude system, temp and accessory tables
    If Left(tdf.Name, 4) <> "MSYS" _
      And Left(tdf.Name, 1) <> "z" _
      And Left(tdf.Name, 1) <> "~" Then

      rst.AddNew
      rst!tableName = tdf.Name
      rst.Update
    End If
  Next tdf

  'cleanup
  rst.Close
  Set rst = Nothing
  Set tdf = Nothing
  Set dbs = Nothing
End Function

The second routine (and the ztblQueries table) are documented in the previous article. The code runs through all queries, writing the query name, SQL and query type to ztblQueries.

Once these two routines have run, the third routine (below) populates ztblReferencedQueries. 

Function ReferencesInQueries()
  ''=========================================================''
  ''Runs through query names in ztblQueries to see what tables
  ''and queries are referenced. Any that are found are written
  ''out to ztblReferencedQueries
  ''Written 11 Sep 2007 by Denis Wright
  ''Modified 12 Sep 2007 to account for parent and child references
  ''=========================================================''
  Dim dbs As DAO.Database
  Dim rstMain As DAO.Recordset
  Dim rstTable As DAO.Recordset
  Dim rstQuery As DAO.Recordset
  Dim x, y 'arrays to hold split data
  Dim sCheckParent As String, _
      sCheckChild As String
  Dim bChild As Boolean

  'define recordsets
  Set dbs = CurrentDb()
  Set rstMain = dbs.CreateQueryDef("", "SELECT * FROM ztblQueries").OpenRecordset
  Set rstTable = dbs.CreateQueryDef("", "SELECT TableName FROM ztblTables").OpenRecordset
  Set rstQuery = dbs.CreateQueryDef("", "SELECT QueryName FROM ztblQueries").OpenRecordset

  DoCmd.SetWarnings False
  'clean out existing data
  DoCmd.RunSQL "DELETE * FROM ztblReferencedQueries"

  'loop through all queries, checking against table and query names
  With rstMain
    .MoveFirst
    Do Until .EOF
      bChild = False
      If rstMain!QueryType = 80 Then 'Make-table query
        bChild = True
        x = Split(rstMain!QuerySQL, "FROM")
        y = Split(x(0), "INTO")
        sCheckChild = y(1)
        sCheckParent = y(0)
        ElseIf rstMain!QueryType = 64 Then 'Append query
          bChild = True
          x = Split(rstMain!QuerySQL, "SELECT")
          sCheckChild = x(0)
          sCheckParent = x(1)
          Else
            sCheckParent = rstMain!QuerySQL
      End If

      'loop through all table names
      With rstTable
        .MoveFirst
        Do Until .EOF
          If bChild Then
            Call AddRefRecord(sCheckChild, rstTable!tableName, _
             rstMain!QueryName, "Child")
          End If
            Call AddRefRecord(sCheckParent, rstTable!tableName, _
             rstMain!QueryName, "Parent")
        .MoveNext
        Loop
      End With

      'loop through all query names
      With rstQuery
        .MoveFirst
        Do Until .EOF
          If bChild Then
            Call AddRefRecord(sCheckChild, rstQuery!QueryName, _
             rstMain!QueryName, "Child")
          End If
            Call AddRefRecord(sCheckParent, rstQuery!QueryName, _
             rstMain!QueryName, "Parent")
        .MoveNext
        Loop
      End With
    .MoveNext
    Loop
  End With

  DoCmd.SetWarnings True

  'clean up references
  rstQuery.Close
  rstTable.Close
  rstMain.Close
  Set rstQuery = Nothing
  Set rstTable = Nothing
  Set rstMain = Nothing
  Set dbs = Nothing
End Function

To describe what the code is doing, let's take a look at how it deals with a make-table query. Assuming that we have a query called qmakCustomers_AtoM, with this SQL:

SELECT Customers.*, Customers.CompanyName INTO Customers_AtoM
FROM Customers
WHERE (((Customers.CompanyName)<"N*"));

This part of the code splits the SQL statement into three sections (Split takes an input string and a delimiter, and creates a zero-based array of the substrings):

      If rstMain!QueryType = 80 Then 'Make-table query
        bChild = True
        x = Split(rstMain!QuerySQL, "FROM")
        y = Split(x(0), "INTO")
        sCheckChild = y(1)
        sCheckParent = y(0)

The first split results in the following 2 strings:

Array element String
x(0) SELECT Customers.*, Customers.CompanyName INTO Customers_AtoM
x(1) Customers
WHERE (((Customers.CompanyName)<"N*"));

The first array element holds the two pieces of information that we want from the SQL: the parent (source) and the child (dependent table / query). By splitting x(0) again using INTO as the delimiter, we end up with:

Array element String
y(0) SELECT Customers.*, Customers.CompanyName
y(1) Customers_AtoM

These two elements are then processed by the AddRefRecord function, which creates a new record in ztblReferencedQueries for each parent / child pair. The code for AddRefRecord is shown below. The error handling creates the table if it doesn't already exist, and the INSERT statement adds the new record. 

Function AddRefRecord(sTestString As String, sCompareString As String, sQueryName As String, _
  sRelationship As String) As Boolean
  Dim sSQL As String

  'this SQL creates ztblReferencedQueries on the fly if it doesn't exist
  sSQL = "CREATE TABLE ztblReferencedQueries ( " _
    & "RefID COUNTER, " _
    & "ObjectName TEXT(225), " _
    & "RefName TEXT (255), " _
    & "Relationship TEXT(30)); "

  On Error GoTo Err_Handle

  AddRefRecord = False
  If InStr(1, sTestString, sCompareString) > 0 Then
    'add the new record
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO ztblReferencedQueries ( ObjectName,RefName,Relationship,ObjectType ) " _
    & "VALUES ('" & sQueryName & "','" & sCompareString & "' _
    & ",'" & sRelationship & "','" & sObjectType & "')"
    DoCmd.SetWarnings True
    AddRefRecord = True
  End If
  
Err_Exit:
  Exit Function
Err_Handle:
  Select Case Err.Number
    Case 3192 'Table does not exist
    'create the table, then continue
    DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL
    DoCmd.SetWarnings True
    Resume Next
  Case Else
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Err_Exit
  End Select
End Function