Overview
ACCESS Dependency Checker is a free Microsoft Access Add-In that reveals the dependencies between database objects like tables, queries, macros etc. and helps to maintain and clean up your MS Access databases.
Key features are
- Finds more dependencies than the Object Dependencies feature does. Shows all tables, queries, macros, forms, reports, VBA procedures, custom menus and their dependencies in one treeview
- Identifies orphanded and broken objects like a query that is based on an already deleted table
- Allows to export the result in various formats (plain text, Excel, Access) for documentation or further processing
- Displays detailed information about any database object and its dependencies at a mouseclick (including beautified SQL definitions of queries, record sources etc.)
- Allows to jump to the object's design to fix problems
- Real SQL parsing to determine the dependencies, therefore independent of the problematic Name AutoCorrect feature and works with MS Access 2000/2002Works with all MS Access versions starting with Access 2000
- This tool is free and open source (GPL)
Latest stable version of ACCESS Dependency Checker: 2.0.1 |
If you encounter a "not sufficient rights" problem during installation, please have a look at the FAQ.
If you use the 64-bit version of Microsoft Access and encounter problems with the version above, you can go to the download page and download a version that should work on every 64-bit system (but with some limitations).
=== marketing break ========
If you really like this tool, have a stack overflow account and would like to promote ADC, please consider to upvote the recommendation in the answer of Rami Sedhom to this question:
https://stackoverflow.com/questions/906918/ms-access-how-can-i-find-out-where-tables-queries-macros-etc-are-used
This Reference on Stack Overflow is by now the second commonest incoming URLs just second to Google, despite the fact that it has zero upvotes (as of June 4th, 2017).
=== end of marketing break ===
News
04-Jun-2017: Release of ADC 2.0.1
Just a minor bug fix (regading empty cell controls on forms) and some internal clean up.
03-Jan-2016: Release of ADC 2.0.0 (wide-screen mode and 64-bit ready)
Finally, after a major rewrite and the longest bug hunt in my coding history, a 64-bit version of ADC is ready. Let me tell you, its no fun to develop for a configuration you cannot test yourself...
There are no ActiveX controls in 64-bit Office, so I had to replace them all. Credits go to Jan Karel Pieterse for creating a treeview control in VBA and to the honorable Jeffrey Fitts, holder of the ADC tester badge in platinum, who helped me to drill down to the real problem. I would like to also mention the numerous users that also contributed on the lengthy path to the 64-bit version.
If you have a big screen, you will love the new wide screen mode giving you all data on one form. Besides that ADC now covers a number of elements introduced with newer versions of Acess.
As always: Let me know if something does not work for you, or if you have ideas to make ADC even better.
22-Aug-2014: Started a FAQ
I started a little FAQ section that might help you, if you encounter a common question or problem. Check it out here.
08-Mar-2014: Beta testers with 64-bit Access wanted
I would like to release a version for 64-bit Access (requiring major changes) but unfortunately I do not have a 64-bit machine with Access installed available. I am pretty close to something that should work, but I cannot test it. If you have access to a 64-bit Access installation and would like to assist, please drop me a note.
08-Mar-2014: Release of ADC 1.0.2
The next bug fix release is out. Should address a couple of issues that several users had. In addition some more minor fixes and improvements to the analysis.
24-Apr-2013: Release of ADC 1.0.1
Today I published a bug fix release. "Export into clean DB" should now work better for many databases. And as always, I fixed a couple bugs that you, my dear users, have brought to my attention.
04-Nov-2012: Issue with MSCOMCTL.OCX caused by security update
In case that you encountered a strange behaviour of ADC after Aug 14th, it might be caused by a security update that broke the MSCOMCTL.OCX (needed for the treeview).
A typical error message would be "The expression MouseMove you entered as the event property setting produced the following error: There was an error loading ActiveX control on one of your forms or reports."
If so, please check the following KB article for a fix:
http://support.microsoft.com/kb/2597986
30-Dec-2011: Release of ADC 1.0.0
Mission accomplished! Pretty close, but I set the goal to release the 1.0.0 version in 2011 and here it is. Key improvement is the more or less completed "Export into clean DB" feature. Have a look. It's worth a try if you have a cluttered DB and look for a quick and effective way to clean it up. As always some bugs have been fixed in addition.
29-Jul-2011: Mysterious bug (Embedded Macros in A2007/2010)
I got three independent bug reports with the following description:
Form/Module: modADC_Parser4Macros
Control/Procedure: parseMacro
Error: #5 - Invalid procedure call or argument
Line: 300
Custom Message: Error while parsing macro 'OnClick![Embedded Macro]'
Unfortunately I was not able to convince the originators to assist further with the bug hunting. At my end embedded macros are handled just fine. If you get this error and are willing to assist in fixing this, please drop me a note.
10-Apr-2011: Release of ADC 1.0.0b
Finally I herewith anounce the release of ADC 1.0.0b, the final beta before I dare to switch the major version number to '1'. This tool will never be done, but bug reports have diminished, though more and more of you use it. So I think ADC is now ready for prime time.
I also encountered the rare intersection of time and motivation to update the documentation this weekend. (Why is writing text so much harder than writing code?). RTFM guys and gals, or you might miss some of the neat features that ADC already has.
Greetings to all of you that contributed to this tool with bug reports, validation of fixes, suggestions and other mail conversations. Please continue to do so.
05-Mar-2011
If you wondered if this tool is still alive, I can tell you that it is. I am preparing a final 0.x.x version with a number of new features and fixes. In addition I am currently working on the documentation that is awfully out of date. Stay tuned (or subscribe to the newsletter) to get notifed when it will be available.
26-Sep-2010: Release of ADC 0.9.10
A bug fix release 0.9.10 is necessary as the expanded coverage of events since ADC 0.9.9 revealed problems with the new embedded macros introduced in Access 2007. Some additional minor fixes are listed in the change log on the download page.
16-Sep-2010
Just a note: The "Load from DB" feature in ADC 0.9.9 is not backwards compatible. If you stored data with ADC 0.98 or earlier, you have to re-run the analysis first (thanks, Sha, for the hint).
25-Aug-2010: Release of ADC 0.9.9
Here we are with ADC 0.9.9, the last step before V1.0.0 will be ready for prime time later this year. Again a bunch of improvements and fixes plus greatly enhanced export features should make it worthwile to upgrade. As always you find more details in the change log on the Download page. Enjoy and do not forget to vote for post 1.0.0 features you would love to see in ADC.
10-Aug-2010
The Heisenbug (see below) has been found and fixed. Credits for the fix go to Michael Wilson who provided a corrupt ADC that revealed the root cause. The fix will be in the upcoming release this month. If this special bug nags you, send me a mail and you will get a pre-release.
13-Jun-2010
It seems that ADC suffers from a Heisenbug. I got two identical reports about an error that cannot be reproduced. It looks like this:
Form/Module: Form_fADC_TreeViewSubform
Control/Procedure: loadTreeView
Error: #91 - Object variable or With block variable not set
Line: 140
If you encounter this error message, please close down ADC, dig out the Add-In (usually located in %Your Profile%\Application Data\Microsoft\AddIns) and send it to me. There might be a corrupted configuration set that causes this, at least I have no better guess yet. Any hints and observations you make regarding this issue are most welcome.
18-Apr-2010: Release of ADC 0.9.8
Version 0.9.8 is out with a pretty long list of improvements (see the Change log for details). Again more dependencies are found, now covering custom menus and their links, event procedures of ActiveX controls (like tree views) and more. Also a number of small and not so small bugs are fixed. Special thanks go to Patrick Sheehan this time for numerous (excellently prepared) bug reports and suggestions for improvements. This version would not be as good as it is without him.
Note: A (not so small) bug in ADC versions 0.9.5 up to 0.9.7 might have created custom menus called "cmbObjectList" and/or "cmbTVContextMenu" in the DBs you have analyzed. The latest version will delete them silently after some additional cross-checking to ensure that they have been really created by ADC.
12-Dec-2009: Release of ADC 0.9.7
Version 0.9.7 fixes a serious bug in the new "Store in DB" feature. Kudos to Sha for reporting this and assisting during the fix.
I also started a page with related links.
29-Nov-2009: Release of ADC 0.9.6
As requested by some users with very large DBs, the new version 0.9.6 allows now to store the analysis result in the database. This data can be loaded from the Startup Screen of the addin instead of running the full analysis, what is much quicker for large DBs.
In addition the tool now reports functions called by custom menus if they are defined with VBA code.
I think I also fixed the random "#91: Object or With block variable not set" errors that several users reported. This plus some minor bug fixes make up the new version. Enjoy.
13-Nov-2009: Release of ADC 0.9.5
Version 0.9.5 is now availabe at the download page. After looking at the change log, I decided to make it a .5 release, skipping 0.9.4. Most significant changes are in the GUI (especially the tree views) that got a lot additional functionality. VBA parsing is now 'reasonably' safe (should find all dependencies but might generate false positives, if different objects share the same name) and of course a number of bug fixes. Thanks to all who provided bug reports and helped me to fix things.
06-Jul-2009: Release of ADC 0.9.3
I am back after being a while away from this tool (new job, new town). Version 0.9.3 is now available at the download page.
I am back to one, uncompiled version as the tree view control in an MDE created all kinds of havoc on non-A2K systems.
The tool now finds custom VBA functions used in queries and other SQL based objects. Besides that some bugs are fixed and there is a "Debug mode" option on the startup screen to easily disable the error handling for ye bold bug hunters.
I also added more explanations to the .
24-Feb-2009: Release of ADC 0.9.2
Added an option for 'optimistic parsing' to find more dependencies with the risk of false links. Should work pretty well if your database objects have unique names; handle with care if not. From now on also a special Access 2007 version is provided (actually same code, just in ACCDE format).
02-Feb-2009: Release of ADC 0.9.1
This tool has now a new name (to avoid a potential conflict with a commercial tool) and a dedicated domain. Some minor improvements make up the 0.9.1 version.
30-Jan-2009: Release of ADC 0.9.0
Finally 0.9.0 is done. Tons of improvements (code/form/macro parsing rewritten; many bugs killed) so better upgrade to this version.
01-Oct-2008
Maintanance release 0.8.6 is available at the Download page. The detailed table analysis is now optional as it can be quite time consuming with linked tables. See the change log there for the other improvements.
03-Jul-2008
Version 0.8.5 has been released. Not enough improvements for a full version step but fixed several bugs and added support for reports. You find the detailed change log and an updated road map at the Download page as always.
22-Mar-2008
I added a describing the features. May serve as a preliminary documentation together with the page.
20-Mar-2008
Version 0.8.0 has been released. Finds now even more dependencies and has a bunch of bugs fixed. Have a look at the Download page for details.
02-Feb-2008
Version 0.7.1 has been released (should work on more configurations than 0.7.0)
14-Jan-2008
First published version 0.7.0 of the MDB-Analyzer has been released