This is a posting I never thought I would actually do. One of the interesting side effects that came with
understanding the information that follows is that it all seemed to just bore me. In other words, the more
I got to know about it, the less interesting it actually was.
With that said, the information is all valid. I found it as a side effect of the work I did on the Access
wizards, for Microsoft. wizard work. Wizards are the original MDEs -- heck, they were MDEs back in Access 95
when they did not even had a name. This is a plan I suggested for dealing with the problem as it applied to
wizards. It was rejected for performance reasons for what was perceived as not being a problem, since
"DAO is always registered" after all. Sucks to be right, sometimes! :-(
This information applies to both MDBs and MDEs, the differences of which I will describe later on.
The issue centers around DISAMBIGUATION -- the process of taking an ambiguous symbol and figuring
out what it means. The goal HERE is to make sure that VBA (and ideally the expression service) does no
disambiguation whatsoever until you are sure that all dependencies in your application are present and
Sounds simple? Well, its not. Because some disambiguation is attempted by just calling a VBA function
(first item on the references list!) like Left$ -- an error that many people will recognize as the famous
"Compile Error: Can't Find Project or Library" discussed in
that's not all; it can be triggered by the loading of a code module even if the code being run does not have
the problem but some other function in the module or class does. Even the Jet expression service (ES) used
by Jet and also by form/report expressions can try to have VBA disambiguate and fail as well. In the end,
you can see any of a number of bad results all caused by having some reference that is not properly set up
on the machine. Here is the step-by-step list that explains how to make sure it never comes to that:
Make sure that you start with a one-line autoexec macro that calls an autoexec function -- and do not do
anything else in the macro since you do not want the ES to be used in any sort of expression.
Make every function call to the VBA or Access libraries explicit so that VBA never needs to disambiguate.
To do this, always use Access.*, Access.Application.*, VBA.*, etc. in front every call (e.g. VBA.Left$
instead of Left$, VBA.Trim instead of Trim).
Have that module from step #1 contain ONLY routines related to this reference fixup and nothing else; in
fact, to keep it from ever trying to load the "potential call tree" which can still happen though this was
only truly awful in Access 95 and only occasionally back in Access 2000. This is done by isolating
the code in this startup module from the code it calls on completion, through Access.Application.Run or
Do not use any other libraries in this startup code if you can help it -- if you must use other libraries
than use them late bound in this startup module, even if you use them with early binding in other parts
of your app.
Do not load any forms, even hidden ones, especially not ones with custom controls on them -- you will find
control sites not hooked up which causes all kinds of problems. This is stuff for later, after you have
fixed up references. The error this can cause will be familiar to many: "You entered an expression that
has an invalid reference to the property __________" (fill in the blank with on-click or some
DO NOT USE THE EXPRESSION SERVICE until the very end when you use Access.Application.Run or
Access.Application.Eval to call your application's conventional startup code. You do not want Access to
realize the bad references are in place.
Don't dawdle! Starting with Access 97, lots of stuff is delay loaded, so the sooner you fix things
up, the better. If you fix before Access loads stuff, things work even better.
Using the above rules, and knowing what your app depends on, proceed to check all the references. Usually
you would do this either through checking the late bound creation of objects or checking the registry.
Check each dependency and do not skip anything. Do not check it by looking at project references or that
IsBroken prop, etc.
If any dependent reference is indeed broken, then fix it. Register it (do it via Declare not by shelling
regsvr32 so you can check the return value) or whatever method is needed. If the file is not in a system
directory, you can use the LoadLibrary/FreeLibrary APIs to load the file prior to calling the
DllRegisterServer function. The syntax for the registration Declaration is:
Declare Function DllRegisterServer Lib "<yourlib.dll>" () As Long
CHECK TO MAKE SURE step #9 worked. If it does not, then you cannot proceed further. All you can do is
report the information about the config and what cannot be saved, and then exit out, letting wiser heads
deal with fixing things. At this point, many of the cool tools like the
Reference Wizard can come
An additional source of pain is runtime error 429 (ActiveX component can't create object), which is
known to often occur with improper DAO configuration, requiring re-registation of various files. This
problem is described throughout the knowledge base (see
The problem with this error is that the references seem okay here. There are two ways to deal with this
problem: (1) always re-register DAO on the startup of your application, using code
like in step #8 (you can see what that code looks like by downloading the
RegisterDao module). Or, if running this every time annoys
you, then (2) use the principles in step #10 to check that DAO is working. How? Simple! Just call the
VBA.CreateObject function with the "DAO.DBEngine.35" or "DAO.DBEngine.36" string and
see if you get an error 429. Once again, this protects you from reference errors while you check for
the integrity of your references.
With MDEs, the problem is a little less nasty than the above, as you can usually get away with running
code until you hit a bad line (which is different than MDBs, which can hit problems just because a module
was loaded). However, it is best to not take chances here and always go through the full steps. The key,
as always, is to fix things up before Access ever has the chance to try loading or executing anything that
depends on a potentially broken reference.
UPDATE 08 Jun 2001: Here is a fun trick for Access 2000! It turns out that you can use the free
TSI ImpExp Spec Tool
to solve the last big problem that can plague an application: broken references with other database files!
The way it works is that using the TSI tool causes Access to load the VBA project of the database you specify
(among other things). Once it is loaded, your main project can find that library, even if it is ordinarily
expecting it to be on another path. All the rules above apply even more than usual, since this type of fixup
will only work if you make sure it happens before you have caused VBA to notice the broken reference.
Note that you can even use VBA.InputBox to query the user for the location of the file and VBA.Dir$ to
make sure the file location is valid so you can properly assure yourself that the load has happened. I have
even tested this technique where both files were MDEs and ADEs, and in all cases it worked beautifully!
Note: For people who really would HATE to require the extra file, licensing the source for
the add-in is possible, send mail to firstname.lastname@example.org
for details -- but please note that you do not have to do this to solve the problem -- most people do not
need to consider spending any money here at all for the ability to shed all of the usual fears about