English - United States  


Usenet Posting #26 - Trigeminal Software, Inc. (English)

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!

Note: For people who really would HATE to require the extra file, licensing the source for the add-in is possible, send mail to reference@trigeminal.com 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 references. :-)


Problems with this site? Please contact the webmaster@trigeminal.com
with your comments, questions, or suggestions.