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

 

[brucewmoore]

You can actually compile from within VBA code . . .

About a year ago, I was researching the traffic on my web site, and one thing led to another. I had re-entered the search terms that I originally used to find the information for my "lost folder" solution.

This was in the second link down. First link was to my web site. The compiling was incidental to what they were doing, which was replacing references.

I was looking over this information, and noticed that they were discussing pre-compile and post-compile issues. I wondered to myself how they were compiling, and low and behold, when I did a search on the page for the word compile, I found this:

appAccess.SysCmd 504, 16483 '<- undocumented call for Compile All without a module open

See following page to find this:

http://bytes.com/topic/access/answers/189771-changing-library-reference

A little research yields:

SysCmd(504, Flag) - COMPILE VBA MODULES

Where flag can be 16483 to indicate 'Save VBA with compiled code' or 16484 for 'Save VBA without compiled code'.

Flag can also be 16481 and 16482 but the functionality is unknown at present.

and:

http://support.microsoft.com/kb/323203

At first, I was thinking in terms of using this to make MDEs and ADEs. I slowly realized, however, that it might not help for that. It does, however, solve the problem that I had with apparently broken references after changing the references in MDBs.

I had previously solved that problem by making sure to explicitly delete the reference to the published version of the program on the network (the one that was available to the public for download). I then re-added the reference at run-time, with a new one pointing to the library in the users home folder.

Now, I should be able to dispense with doing reference changing at run time, and may be able to just make sure that the network master gets recompiled after it has been published.

Also, see my method for getting compiled Access programs to find references at run time:

http://www.mooresw.com/get_ms_access_to_dynamically_search_for_references_from_compiled_code.php

Later,
Bruce