It sounds like you have reference problems. Note this from
a previous posting and some URL's that might help you
Jim
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html
http://members.iinet.net.au/~allenbrowne/ser-38.html
http://www.trigeminal.com/usenet/usenet026.asp
Subject: INFO: How to guarantee that references will work
in your applications
(Originally posted 6/06/01 in a somewhat different form)
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 properly registered.
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 Q160870.
And 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:
1. 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.
2. 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).
3. 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 Access.Application.Eval.
4. 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.
5. 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 other property).
6. 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.
7. 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.
8. 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.
9. 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
10. 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 in handy.
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 Q189366 and Q296205 for
more information).
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!
