Home Page
Tips And Tricks
Helpful Links
e-mail me

Picture of building from outside Tips And Tricks

I seem to run into things from time to time that I want to share with the world, usually things that I had to find out the hard way. Sometimes it will just be something that I had to dig for, and want to make more readily accessible. This page is intended to make life a little easier for other programmers. If it meets your need, then that's what it's here for.

Get MS Access to Dynamically Search For References From Compiled Code
Ok, so we had our batch files set up to publish new versions calling code to change references. Then, we ran into another problem. At first, we found ways of dealing with it, but the problem became more and more annoying over a period of months while I tried to find a better way.

When using the above code to change a reference, it works fine on ADPs. However, due to customer request, we were using ADEs, or in other words, compiled code. After trying several different ways of making the above code work for the compiled ADEs, we realized that programmatic changes are not allowed to references in compiled code.

Well, that little roadblock was eventually causing us all kinds of problems. At one point, we found that all the end-user front ends were pointing to a file in a developer's home folder. You can imagine the kinds of problems that causes, such as being unable to publish new versions until everyone gets out of the program, etc. Besides, programs running in MS Windows are supposed to be able to dynamically search for DLLs, right?

I searched and dug, and searched some more for solutions to this problem. I studied late-binding without actually implementing it (although I did test a small pilot project). And honestly, I'm not sure at this point whether that might also be another solution to my problem. However, in my experience so far at least, late binding is not only tricky to do, but is a little difficult to even understand. I seriously considered the possibility of making my library into an "Add-in", which did not seem like an ideal solution. (Those can be programmically controlled too, but that is a whole article in and of itself)

Finally, I was able to find an obscure description of how to get Microsoft Access to dynamically search for a file pointed to by a reference at run time. I searched for the link again at the time of this writing, but was still unable to easily find it by searching, even though I already knew the solution to my problem. I ended up having to go back and find it in the email that I sent to my helper about the subject. Remember, this link was the culmination of literally months of searching for a solution to my problem.

The link where I found my solution is:


I had recorded in my email that I found this link using search terms (Microsoft Access "compiled library") The answer was buried ten posts down, and the thread starts out with a discussion of how to open reports in another database. Although the fellow who enlightened us did not sign his last name, I suspect that it may have been a fellow named Steve Young, who hails from Canada. The description that I did find was highly technical, and seemed hard to follow, at the time. So I resolved that I would try to put it down in my own words, and hope that it is easier for you to find, and also easier for you to understand.

The way this works is what I refer to as the "Lost Folder" method. As long as Microsoft Access has an exact reference to an MDB or an ADP, (or a DLL, for that matter) it is happy. It "latches on" to that reference, and once the MDB or ADP gets compiled into an MDE or ADE, I have not yet found a way for it to be changed programmatically. If, however, the file referred to is not found in the place where it was at compile time, Access, (and other Windows programs) will search for it. There are descriptions of how this works in relation to various compilers, such as "C" compilers, etc. These behaviors are more commonly discussed in relation to EXEs and DLLs. (See http://support.microsoft.com/kb/824255)

The various descriptions that you find of how this works talk about which folders will be searched first, such as the current folder. If the original MDE or ADE file still exists in the location pointed to by the reference, (like in the programmer development area on a network) this "search" mechanism doesn't kick in. In that case, there's no reason for searching. But most programmers do want to have both a development environment and a production environment, so it becomes crucial to find a way to get Microsoft Access to look for the library in the current directory at run time.

I suppose that it would be possible to place the source code in the production area and compile it there, but that has it's own set of problems. Remember, this is a front end we are talking about. We didn't really want everybody's front end to refer back to (and tie up) the main copy of the program library on the server.

So what we have to do here is to make absolutely certain that the library the program is compiled against will not ever be found in the original location. By doing that, we can coerce Microsoft Access to look for our compiled program library in the current folder at run time, and get it to use the library by the same name that it finds there.

This goal can be accomplished by compiling the program in a temporary folder, or "lost folder", which will be deleted after the program has been published, and which will not ever be found again after that. When each user runs the front end, that user's ADE will search for and find the library in the current folder where the program is running, and will not "latch on" to the main copy of the compiled program library that resides on the network in the production area (or in the developer's home folder). The only time that users are accessing the compiled front end and the compiled program library is when they click an icon on their desktop to copy those files to a folder on the user's computer while getting a new version.

What follows is a subset of the batch file that we use to publish our programs to the production area. Adapt this sample of the "lost folder" method to your situation. We use %USERPROFILE% because we had some issues with some other environment variables being too long and causing lines that were longer than 255 characters, which the command prompt choked on.

Note: The complete version of this batch file also copies and compiles the main front end program as well as the library. The library must be compiled first, and then the main program, so that a reference can be set up in the main program to refer to the library ADE. I've left out the lines which compile the main program for the sake of making this example more readable.
:establish an environment variable composed of the folder name, date, and time
set tFTime=%time:~0,2%%time:~3,2%
set tFTime=%tFTime: =0%
set tFolder=MyCompiledProg%date:~10,5%-%date:~4,2%-%date:~7,2%-%tFTime%

:make the new temporary folder on the c: drive
md %tFolder%
cd %tFolder%

:copy the program from the development area into the newly created temporary folder
copy "i:\accdev\ProcLibCS.adp" "%USERPROFILE%\%tFolder%"

:change the connection from development data to the live production data
call c:\windows\runacces "%USERPROFILE%\%tFolder%\ProcLibCS.adp" /x SetUpLiveLinksSilent

:get what's needed for an automatic compile in the temporary folder
copy "I:\accdev\AutoCompile.adp" "%USERPROFILE%\%tFolder%"

:actually compile the program, using a macro which calls code to send keys to the compile
call c:\windows\runacces "%USERPROFILE%\%tFolder%\AutoCompile.adp"
  /x MakePRProcLibADEOnCCurDir /cmd %USERPROFILE%\%tFolder%\ProcLibCS.adp
 (note that this is all one line, not the two lines shown here)
:copy the newly compiled program to the area where users will get new versions
copy "%USERPROFILE%\%tFolder%\ProcLibCS.ade" "i:\ProductionVersionFolder"

:delete the temporary folder where the program was compiled, so that it can't be found
cd ..
rd /s /q %tFolder%
This batch code makes a new temporary folder on the c: drive using the date and time in the name of the folder, which comes close to a guarantee of a unique folder name. It then copies our library, ProcLibCS.adp, to the temp folder and runs the code that we use to change the connection from the development to production data. After that it copies AutoCompile.adp to the new temp folder and runs it to make a newly compiled ProcLibCS.ade. It then copies the newly compiled ProcLibCS.ade from the temporary folder to the location on the network where we are keeping the compiled version for users to copy when they get a new version of the program.

Finally, as the last step, it deletes the temporary folder where the programs were compiled to make absolutely sure that Windows will always dynamically search for ProcLibCS.ade every time the program runs, regardless of what the original reference was.

Thanks again, Steve, for giving me the solution to my problem so that I could post it here in my own words for everyone to see.

If you found this information to be useful, and would like to donate via PayPal to help offset the cost of maintaining this web site, use the following button:

|Home Page| |Custom Software| |Policies| |About| |Tips And Tricks| |Helpful Links|