Finding and Deleting Unused Access Objects and VBA Code
How Unused Objects and Code Get Created
It's very easy to create objects in a Microsoft Access database whether
it's an MDB, ADP, or ACCDB. During development or interactively analyzing
data, queries and code are commonly created and tested. Some are kept for
future use, but others remain even if they're no longer necessary. It's
usually because you don't want to immediately delete these objects in case
you need them in the future. Later, one forgets why the objects exist.
Over time, the accumulation of unnecessary and unused objects can become
significant and distracting. It creates confusion when trying to figure out
how all the database objects work together, and you may end up fixing and
maintaining objects that aren't even used.
Fear of Deleting Unused Objects
Are you afraid to delete an object
you think is unused, but are afraid to do so because you're not
Do you rename object names then run your program to see if it
crashes, because you think it's unnecessary but are not
These are common experiences among MS Access users and developers.
Fortunately, there's a better way than just hoping you didn't make a
Finding Unused Objects
The process of finding unused objects is non-trivial since we're trying
to discover what doesn't exist. In order to determine which objects are not
used, we need to first determine where every object is used (an object or
code cross-reference). Only after that's known, can one determine which
objects are unused.
Where Objects and Code are Used
Microsoft Access objects and code can be used in a variety of places
which makes it very challenging to determine with 100% accuracy that all the
references are detected.
Where Tables and Queries are Referenced
For example, here are some places where Access tables and queries can be
Form and Report References
Similarly, forms and reports can be referenced in a variety of places:
- The Startup form property of the database
- The SourceObject of a subform or subreport
- The parameter of a query that references a control on the
form/report. This can be a standalone query or the SQL string of a
form/report Record Source property or one of its control's Row Source
- A form or report control that references a control on another
- A parameter passed to a VBA or user defined function
- Command bar or Ribbon references
- Macro commands opening a form/report, referencing a control in an
expression or condition, etc.
- Module commands that can open or reference forms or reports in a
variety of ways
- Table driven systems where form and report names are stored in a
- Interactive use of forms and reports outside programmatic control
Module and Procedure References
Unused classes, procedures and variables are very common. Code can be
used not only within modules but also through other objects:
- Macros can call functions via the RunCode command
- Queries can reference user defined functions as criteria or output
- A form or report event can call functions
- A form or report control can call functions in its ControlSource
These non-module code references are very powerful but can also cause
maintenance problems because they are outside the scope of the VBA compiler.
That is, the VBA IDE's compile command can consider the project to be
compiled, but can't detect invalid references to the code from these
non-module places (misspelled function names, invalid or incorrect number of
Temporary Variable References
In Access 2007 and 2010, you can create temporary
variables in macros and modules, and use them almost
anywhere including queries, forms, reports, macros and
modules. Simply reference them with the TempVars![VariableName]
syntax. The problem is variables get created and may never
be referenced, or referenced but not defined. Unlike module
variables, there's no syntax checking to verify your
temporary variables are defined.
Comprehensive Object and Code Cross-Reference with Total Access Analyzer
Fortunately, our Total Access Analyzer program performs detailed analysis
among all the Access objects to generate the most comprehensive usage across
your database. It can't determine certain things like non-explicit object
references in code, references based on table driven systems, or interactive
use of the objects, but it does capture most of the other references and all
explicit references. By determining all the places where objects are used,
it then reports the objects that are not used.
Unused Object and VBA Code Analysis
After you document your database by selecting all your objects (the
object cross-reference wouldn't be comprehensive if you didn't document
everything) and check the module cross-reference option, the results include
object and module cross-reference (XREF) and unused object and code
In addition to unused code, Total Access Analyzer also detects:
- Unreferenced variables, which are variables assigned a value but
never used (why bother?)
- Procedures that should be private rather than public since they are
only used in their module
- Empty procedures (common when an Event Procedure is created but
Viewing the Unused Objects and Code
The results can be viewed through the Documentation Explorer under
When your cursor is on a particular issue, the Design button is enabled
to let you open that object in design mode so you can review and apply the
changes you want to make. This is particularly useful for the unused VBA
code issues since you can jump directly to that procedure in the module
The filter feature lets you specify the suggestion types you want to see:
Unused Object and Code Reports
Total Access Analyzer offers several reports for unused objects:
- Unused Tables
- Unused Queries
- Unused Forms
- Unused Reports
- Unused Macros (including sub macros)
For VBA module code, these unused reports are available:
- Unused Procedures (Subs and Functions)
- Unused Properties (Let, Get, and Set Statements)
- Unused Constants and Variables (local, module and global levels)
- Unused TempVarss
- Unused User Defined Types, Enums and their elements
Total Access Analyzer lets you select these reports under the Suggestions
Eliminating Unused Objects and Code
Just because Total Access Analyzer says an object is not used does not
mean you should automatically delete it.
First of all, a table, query, form, report, or macro can be used
interactively from the Navigation Pane or database container and would not
be detected by Total Access Analyzer. So this requires knowledge of how the
database is used by the users and developers before deleting them.
Secondly, while it's pretty easy to confirm a variable isn't being used
in code, it's not so clear with other objects. You need to confirm that
objects aren't being used in ways that Total Access Analyzer can't detect.
An example would be references based on values in a table or expressions in
code, or usage by another application.
Assuming you've determined which items are not used, you are ready to
delete them. Be sure to make a backup, then test your new database after
It's also a good idea to rerun Total Access Analyzer, because after you
delete your objects, there may be new unused objects which were only
referenced by your deleted objects.
Determining where Access objects are used and getting rid of unused
objects was one of the main reasons we originally created Total Access
Analyzer. It's a fundamental part of professional application development,
and we're pleased so many Access users and developers use our product. We
hope you'll give it a try as well.
Some think that getting rid of unused objects and code is an unneeded
luxury since these objects don't harm anything. That's true, but creating
professional solutions involves doing things right, keeping things clean,
and adopting Best Practices.
Eliminating unused objects and code in your database can significantly
reduce the bloat and maintenance hassles of your Microsoft Access
applications. It makes the database easier to understand, reduces the need
to fix useless objects and code, and improves performance.
Getting rid of unused Access objects is one of the first things we do
when taking over an existing database. We also use it during development to
make sure we don't introduce unnecessary junk. During development, it's very
easy to create and abandon queries, procedures, variables etc., so the less
of those we keep, the better.
Best Practices for Microsoft Access Database Development