|
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:
http://www.developersdex.com/vb/message.asp?p=2899&r=6444728
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
c:
cd %USERPROFILE%
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
c:
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:
|
|
|
|
