Setting References Programmatically

[Matt]

Hi all,

I have a general question about setting references
programmatically: If you set a reference from within a
procedure in VBA/Access, and then follow it by code that
utilizes objects from the newly referenced item, won't
that code always fail to compile since the reference is
not pre-defined in the project?

I am having this problem in one Access database, when I
programmatically set a reference to another Access
database and then try to call a public procedure that is
located in a module within the external database.

Matt

 

[TC]

re. your last paragraph, what is the code that is failing, & how does it
fail?

TC

 

[Geoff]

Yes, the apparent paradox is that you cannot run
your code (to establish the reference to the library
database) until your code is compiled, and you
cannot compile your code while it relies on a
reference that does not yet exist.

If you must establish the reference by code,
then a solution would be to call the procedures in
the library database using Application.Run, which
takes string arguments. Then your code will
compile, allowing the reference to be made.

Incidentally, you can prevent references from being
lost if VBA has a way of finding library databases.
VBA searches for library databases in:

* absolute and relative paths,
* the folder where Access is installed,
* the Windows System folder,
* all folders included in the environment PATH variable,
* and folders in the appropriate Registry key, eg:
HKEY_LOCAL_MACHINE\Software\Microsoft\
Office\9.0\Access

If using the Registry, for each library database,
create a new string value for the name of the
library and a value for the path to that database.

Regards
Geoff