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

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

A table for the relationship data: ztblReferencedQueries

A table that lists the tables in the database: ztblTables

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:

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:

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

 
Home | About |
Links |

Contact | Excel |
Access