If you have to document the queries on your database you will often need to know:
In an earlier article I showed how to document query SQL. This article builds on that documentation to answer the second question.
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.
To view object dependencies in Access 2003, do the following:
To turn off Name AutoCorrect do the following:
In Access 2007, you turn off Name Autocorrect by doing the following:
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.
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.
Field | Data Type | Comments |
---|---|---|
RefID | AutoNumber | Primary Key |
QueryName | Text (255) | |
RefName | Text (255) | |
Relationship | Text (50) |
Field | Data Type | Comments |
---|---|---|
TableName | Text (255) |
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