Documentation

Table of Content

The Startup Screen

Startup Screen

When you start the Add-In, you will see the startup screen where you can define what to analyze (and what not) and set some options.

Select Objects to analyze

You can use the checkboxes to determine what objects shall be analyzed. The buttons at the bottom provide shortcuts to select/deselect all and limit the analysis to tables and queries.

Options

Show SQL as is

SQL as Access creates it is barely readable due to missing indenting and line feeds. The tool "beautifies" the SQL of queries, record sources etc. that is displayed on the Database Object Window (see there for an example). Enable this option if you want the SQL as stored in Access (also slightly faster).

Show Table Details for remote Tables

Linked tables on network drives may take very long to analyze, depending on the network speed. Default is "off". Enable this option if you want to see the table structure of remote tables. Please be aware that you also need to enable this option to parse SQL in field definitions of remote tables.

Strict Parsing

Some link checks are not 100% safe. E.g. doing a text search for a table name in a code module might bring up a false link, if you have a variable with the same name as the table. In the default setting the tool reports rather too many than too few dependencies. If you prefer it the other way around, you can enable this option.

Wide Screen Mode

Will combine the main form and the database object form into one. Needs some screen real estate, but any descent monitor should be wide enough.

Debug Mode

Normally run time errors are handled and a pop-up form indicates the problem with some details. If you are in the bug hunting business, you might want to enable the debug mode to hunt 'em down. This is an open-source project by purpose.

Load Analysis from DB

If an analysis has been stored in the DB in a previous session, it can be loaded with the "Load from DB" button, instead of running the (possibly lengthly) analysis again. For more details see below about how to store an analysis.

Running an Analysis

Clicking OK in the startup screen will start the analysis. A little progress window will tell you what happens. You can cancel the analysis at any time by closing the progess window.

What is analyzed

The tool analyzes tables, queries, macros, forms, reports, controls, code modules and custom menus. See below what dependencies are covered.

Tables

RowSource of Field lookups

Queries

All Query types (including Action, Union and Data Definition Queries)* are covered. PassThrough queries are reported but not parsed, due to the (typically) non-JET SQL. Subqueries* are also parsed.

Macros*

All macro actions are reported. The following ones are also parsed for dependencies.

  • Opening or executing DB objects (OpenQuery, OpenTable, OpenForm, OpenReport, RunMacro, RunCode, RunSQL)
  • Import/Export actions (TransferText, TransferSpreadsheet, TransferDatabase, OutputTo, SendObject, CopyObject)
  • Other actions that use DB objects (Close, GoToRecord, DeleteObject, RepaintObject, Save, SearchForRecord, SelectObject, ApplyFilter)
Forms/Reports
  • RecordSources (for bound Forms)
  • All Form/Report events
  • Form/Report section events (header, footer, details sections)
Controls
  • RowSources of ListBox and ComboBox Controls
  • Links to SubForms/Reports
  • Dependencies via HyperlinkSubAddress property of Command Button, Image and Label Controls
  • All Control Events
Modules*
  • Code-to-code links (calling of functions and subs, usage of custom classes)
  • Usage of other DB objects (DoCmd.OpenQuery, DoCmd.OpenTable, TableDefs, QueryDefs, DoCmd.OpenForm, DoCmd.OpenReport, DoCmd.RunMacro, DoCmd.RunSQL)
  • Functions used in queries (or other SQL based objects)
  • Functions as targets of custom menus if defined with .OnAction in VBA
  • All occurrences of object names in the source code are reported if "Strict parsing" has not been selected in the options (default)
Custom Menus*

Toolbars, Commandbars and Shortcut Menus are parsed. Object specific menus are linked to that object (like a popup menu for a control or a menu bar for a form).

* not reported by Access 2003+ Object Dependencies feature

The Main Window

When the analysis is complete, the main window will pop up. You now can browse, analyze and export the result. The window is resizable.

The Treeview

The database objects (tables, queries etc.) are organized in a treeview on the right. Check the treeview documentation for details. Double clicking a node opens the DB Object Window with details about the selected object.

The Object-List and Search Features

You can search by the name of an object, search the content or use one of the predefined special searches. The result will always be shown in the listbox in the lower left corner. Double-clicking an entry will open the DB Object Window with details.

Find DB Object(s)

Here you can search for objects by their names. Wildcards like '?' (one char) and '*' (any number of chars) can be used.

Search in Objects

This search object scans the content of objects. This allows to e.g. search the code for occurences of a certain string. Please note that the content of tables will not be searched, only the table definition.

Find Special

A number of special searches has been predefined that can be selected from a drop-down. There are "All Objects By Name", "All Objects By Type and Name" (different sorting), "All <object type>",  "Top/Unused Items", "Missing Sorces" and "Missing Targets"

The context menu of the object-list

Right-clicking an entry in the object list offers the following options:

  • Show Details: Opens the object in the DB Object Window (same as double-clicking an entry)
  • Set as Root for Treeview: Seeds the treeview to the right with the given object so that you can browse its dependencies
  • Open in Design View: Opens the selected object in design mode to apply changes. If the object cannot be opened itself (like a form control or a macro action) its parent will be opened in design mode.
  • Search in Objects: Searches the content of all objects for the name of the selected entry.
  • Export List to Excel: Exports the object list to a new Excel workbook for documentation for further analysis

Refresh

You might use the tool to clean up a messy DB, e.g. by going thru the missing objects list, therefore the DB might change while using the add-in. As a shortcut you can re-run the analysis directly from the main window by clicking the Refresh button.

Export

You can export the result of the analysis in various styles and formats. Clicking the Export button brings up the export dialog. Usually you can select to add a timestamp to the filename or table name to keep the history of exports.

Export Analysis Data

Allows to export the result of the analysis. For the export target you can choose Access, Excel or a text file.

Exporting to Access will store two tables called "ADC_Dependencies[_optional time stamp]" and "ADC_Objects[_optional time stamp]" in the analyzed DB itself.

Here some background about fields that might need explanation:

ADC_Objects
  • ID: Identifies the object when referenced (see below)
  • ParentID: If the object has by definition exactly one parent, this will be the ID like a control can be on only one form (redundant, but allows some shortcuts)
  • Name4Display: extended object name, includes for controls, table fields procedures and macro actions also the name of the parent
  • Missing: Flag (0/-1) if the object is referenced by another object but does not exist in the DB
ADC_Dependencies
  • TopID: The parent or user of another object
  • BottomID: The child or used object
  • Target: Flag (0/-1) if the object is the target of an action query or data import macro action
  • Child: Flag (0/-1) if the BottomID is the child of the TopID (to distinguish from used objects)
  • Order: Sequence number if there is an order like for macro actions in a macro

The export to Excel will create a new workbook with two tabs "Objects" and "Dependencies". Cancelling the "Save as" dialog box will create an unsaved (temporary) Excel workbook. The fields are like the the ACCESS export (see above) but with added data on the dependencies tab. Here a sample with the analysis of ADC 1.0.1.

The text file format is a handy feature if you want to check what in a DB design has changed, as tools like WinMerge or CSDiff can easily detect differences in two text files. Here as an example the export for ADC_1.0.1.

Export Dependency Chains for flagged objects

Sometimes you might want to know how two objects relate what can be cumbersome by browsing the tree view. Therefore this export has been created. This export is always in Excel format. Before you can use this feature, you have to flag an object with the "Flag for export" entry in the tree view context menu. The export itself is independent from the tree view state. If there are different chains for two objects, only the shortest will be reported. The parsing will always be top-down.

Export into cleaned DB

This export is a little different. It does not export information about the DB in use, but creates a new DB with the selected objects and all their dependencies. This allows to strip off all debris in one step.

The algorithm starts with

  • all objects flagged for export
  • AutoExec macro (if available)
  • Form in startup options (if available)

This feature is pretty new so handle with care. Not covered are

  • Im-/Export specs
  • what I have not discovered yet

Send Feedback

This button will guide you directly to the contact form on www.accessdependencychecker.com. Let me know if you are annoyed by something, have an idea for a feature or anything else.

Storing the result in the analyzed DB

The "Store in DB" button writes three hidden tables with the analysis result to your database (tADC_Objects_save, tADC_ObjectLinks_save and tADC_Properties_save). This might save a lot of time for large databases as this data can be loaded from the Startup Screen instead of running the possibly lengthly analysis again. The time stamp of the last full analysis is shown in the upper left area so that you can see how old the data is.

You can remove the three tables from your DB again with the "Clear DB" button (only enabled if there is something to delete).

The Database Object Window

The Database Object Window shows detail about the analyzed objects like the content (e.g. the query SQL) and a detailed overview of the dependencies.

This window is also resizable and you can click and horizontally drag the bar between tree view and content area.

The DB-Object window

The Treeview

The dependencies of the selected object is shown in the treeview on the left. The behaviour of the treeview is identical with the one on the Main Window, besides special context menu entries. Check the treeview documentation for details. Double-clicking an object will set it as the new root for the treeview.

The Content Area

The Content Area shows name, type and subtype of an object and the content, what can be the SQL of a query, the code of a procedure, recordsource and control list for a form and the like.

You can search thru the content of an object (especially handy in large queries or code modules). With the left, right buttons (the small triangles) you can navigate in a history list of displayed objects what also affects the treeview.

It is also possible to open the current object in design mode to fix things.

Browsing the dependencies in the treeview

The Main Window as well as the DB Object Window contain the same treeview object. Everything described here applies to both tree view instances.

Configuring the treeview

Several options allow to configure what the treeview shows. You invoke the configuration menu by clicking the triangle in the upper left corner of the treeview or by clicking the configuration description to the right of it.

Top/Unused Items

Top Items are objects that are not used in "higher" objects. E.g. a form that is not called by another form is a top item. By the same token these objects are "unused" as no other DB object needs them. All objects you see here are usually unreachable thru the GUI of your DB application, what is the equivalent of unreachable code in a program. Exceptions are AutoExec macros and startup forms defined under Extras->Startup...

Missing Objects

Missing objects result from broken dependencies. An example is a query that has been built on a table that has been deleted in the mean time. Then the table is listed as a missing object.

This option is most useful with the "Bottom-Up" selection. Then the missing objects will show the objects that require them as their children.

By Object Type

Will start the tree view with all objects of the same type. Self-explanatory, I hope.

Top-Down

The "Top-Down" options starts the section with the choices how to present the dependencies. "Top-Down" will show the required objects as children to a treeview node. A query, e.g. has the used tables/queries linked as children.

Bottom-Up

Just the opposite of "Top-Down". Helpful to determine what objects use a specific one. Use this option e.g. if you want to determine where a specific table is used.

Up and Down

The combination of "Top-Down" and "Bottom-Up". Shows them both with dependency type headers that separate "up" and "down" links.

All Types

Like "Up and down" but shows all dependency type headers regardless if there are links of that type or not.

Hide Children w/o Links

With the default settings all available dependencies and children are listed. That might be pretty convoluted, e.g. for a form with many controls. Selecting the option will limit the displayed children to the ones that have further dependencies.

Dependency Types

There are six different types of dependencies for an object. These will be shown in the treeviews depending on the selected display mode.

Parents / Children

If there is an umambiguous link between two objects, this is listed as a parent/child relationship:

  • A Field in a Table
  • A Control in a Form/Report
  • An Event linked to a Control or Form/Report
  • An Event Procedure linked to an Event
  • A Form/Report module linked to the Form/Report
  • A Procedure (function or sub) in a Module
  • An object used in an Import/Export Macro action
  • SQL Strings in a Procedure, a Macro or as a RowSource/RecordSource
Used Objects / Used By

This is the obvious one. Tables and Queries are used in other Queries, RowSources, RecordSources, Export Macro actions, etc. "Used By" is the other end of the link. If an object is used by another one, it is listed in this category.

Target Objects / Target Of

Only few objects have targets. These are action Queries and data import macros/commands that have a table as the target of an action. Only Tables can be targets of other objects. "Target Of" is the equivalent complement.

Treeview Context menu

Right-clicking a treeview node offers features that require a specific object as input. The top five entries are identical for both treeviews on Main Window and DB Object Window. Appended are custom entries depending on where you are. The picture on the right shows the treeview context menu on the Main Window.

Show Details

Opens the selected object in the DB Object Window.

Set as Root

Sets the selected object as the root node of the treeview.

Expand Below

Unfolds all children of the selected object. This option is disabled for the treeview styles "Up and Down" and "All Types" as the tree can easily become excessively long.

The tree might even for "Top-Down" and "Bottom-Up" become very large. Therefore every 1000 unfolded nodes you will be asked if you want to continue.

Collapse Below

Re-folds all children at all levels. This is different from clicking the minus-sign left of the node, what would keep the folding state of the children.

Open in Design Mode

Opens the selected object in design mode to apply changes. If the object cannot be opened itself (like a form control or a macro action), its parent will be opened in design mode.

Export to Excel as shown

This features allows you to export the current state of any treeview portion to Excel for documentation purposes or further analysis.

Flag for Export (Main Window only)

As described in the documentation about the possible data exports. Some exports need specific nodes as input. Selecting this menu item flags the selected object for subsequent export steps.

Clear Flag(s) (Main Window only)

Deletes the flags again set with the menu item above.

Search in Objects (Main Window only)

Uses the Search functionality to find objects that contain the name of the selected object.

Find in Content (DB Object Window only)

Searches the name of the selected node in the currently displayed content and highlights the first occurence. Click "Find (Next)" to display occurences further down.


^ Top