[AccessD] libraries, References and some subtleties to be considered

Dan Waters df.waters at comcast.net
Thu Dec 15 16:40:25 CST 2011


Yes - Library referencing can be a pain.

At my customers each client has a shortcut which points to an AutoUpdater
file.  The AutoUpdater file will check to see if the Main and Library files
on the client are older than the files on the server.  If so, it will copy
those files up to the client in the same folder.

But here's the problem - the main file on the client will still reference
the library file on the server.  The way I get around that is to leave the
library file on the server with an XX in the name - that way the main file
on the client can't find it so it re-references to the library file on the
client (they're in the same folder).

When the autoupdater file does its thing, it compares modified dates between
Library.mdb on the client and LibraryXX.mdb on the server.  If the server
has the newer file, then autoupdater will copy and rename the LibraryXX.mdb
file on the server to Library.mdb on the client.

Another problem is that the Library.mdb file is renamed on the server (your
dev/test system), so you can't open it until you manually retype the name,
and then you have to remember to retype it back when you're done.  To solve
that I made a ChangeXX.mdb file.  It has an AutoExec macro which runs the
following code:

'------------------------
Public Function StartupChangeXX()
On Error GoTo EH

    Dim stg As String
    Dim rst As DAO.Recordset
    Dim fso As FileSystemObject
    Dim blnAddXX As Boolean
    Dim stgXXFile As String
    Dim stgExtension As String
    Dim stgPrompt As String
    Dim blnNeedManualPrompt As Boolean
    Dim blnFoundAllClear As Boolean
    Dim blnFoundAllXX As Boolean
    Dim blnFirstLoopComplete As Boolean
    Dim stgSystemMode As String
    
    stgSystemMode = Command()
'    stgSystemMode = "Review"  '-- TEST
    
    Set fso = CreateObject("Scripting.FileSystemObject")

    '-- Do the files exist?
    stg = "SELECT FileFullPath FROM tblParameters" _
        & " WHERE SystemMode = '" & stgSystemMode & "'"
    Set rst = DBEngine(0)(0).OpenRecordset(stg, dbOpenSnapshot)
    Do While rst.EOF = False
    
        stgExtension = fso.GetExtensionName(rst("FileFullPath"))
        stgXXFile = Left(rst("FileFullPath"), Len(rst("FileFullPath")) - 4)
& " XX ." & stgExtension
            
        If fso.FileExists(rst("FileFullPath")) = False And
fso.FileExists(stgXXFile) = False Then
            MsgBox "The file " & rst("FileFullPath") & " does not exist!",
vbExclamation + vbOKOnly, "Missing File"
            rst.Close
            Set rst = Nothing
            Application.Quit
        End If
        
        rst.MoveNext
        
    Loop
    rst.Close
    Set rst = Nothing
    
    
    '-- Are all files clear or all XX?
    stg = "SELECT FileFullPath FROM tblParameters" _
        & " WHERE SystemMode = '" & stgSystemMode & "'"
    Set rst = DBEngine(0)(0).OpenRecordset(stg, dbOpenSnapshot)
    Do While rst.EOF = False
        
        If blnFirstLoopComplete = False Then
            If fso.FileExists(rst("FileFullPath")) = True Then
                blnFoundAllClear = True
                blnAddXX = True
            Else
                blnFoundAllClear = False
                blnAddXX = False
            End If
            blnFirstLoopComplete = True
        Else
            If fso.FileExists(rst("FileFullPath")) = True Then
                If blnFoundAllClear = False Then
                    blnNeedManualPrompt = True
                    Exit Do
                End If
            Else
                If blnFoundAllClear = True Then
                    blnNeedManualPrompt = True
                    Exit Do
                End If
            End If
        End If
            
        rst.MoveNext
        
    Loop
    rst.Close
    Set rst = Nothing
    
    
    '-- Select to add XX or Remove XX
    If blnNeedManualPrompt = True Then
        stgPrompt = "Push Yes to add XX." _
            & vbNewLine & vbNewLine _
            & "Push No to remove XX."
        If MsgBox(stgPrompt, vbQuestion + vbYesNo + vbDefaultButton2,
"Change XX") = vbYes Then
            blnAddXX = True
        Else
            blnAddXX = False
        End If
    End If


    '-- Add or remove XX
    stg = "SELECT FileFullPath FROM tblParameters" _
        & " WHERE SystemMode = '" & stgSystemMode & "'"
    Set rst = DBEngine(0)(0).OpenRecordset(stg, dbOpenSnapshot)
    Do While rst.EOF = False
    
        stgExtension = fso.GetExtensionName(rst("FileFullPath"))
        stgXXFile = Left(rst("FileFullPath"), Len(rst("FileFullPath")) - 4)
& " XX ." & stgExtension
            
        If blnAddXX = True Then
        
            If fso.FileExists(rst("FileFullPath")) = True Then
                fso.CopyFile rst("FileFullPath"), stgXXFile, True
                fso.DeleteFile rst("FileFullPath")
            End If
            
        Else
        
            If fso.FileExists(stgXXFile) = True Then
                fso.CopyFile stgXXFile, rst("FileFullPath"), True
                fso.DeleteFile stgXXFile
            End If
        
        End If
    
        rst.MoveNext
        
    Loop
    rst.Close
    Set rst = Nothing
    
    
    If blnAddXX = True Then
        CreateObject("WScript.Shell").PopUp "Added XX.", 1, "XX Change"
    Else
        CreateObject("WScript.Shell").PopUp "Removed XX.", 1, "XX Change"
    End If
    
    Application.Quit

    Exit Function
    
EH:
    MsgBox "Error!" _
        & vbNewLine & vbNewLine _
        & "Code: " & Err.Number & vbNewLine _
        & "Desc: " & Err.Description & vbNewLine _
        & "Line: " & Erl

End Function
'-----------------------

There is also a tblParameters which contains information about which actual
system I'm working on - I have three at each customer.  Prod, Test, and
Review.  The shortcut on the desktop which opens ChangeXX.mdb has a command
argument (like '/cmd Test') so the code will know which system it's supposed
to be working on.

Also, there is a popup message that stays open for 1 second to tell me
whether it added the XX or removed the XX, then ChangeXX.mdb quits.  

Just click the ChangeXX shortcut on the server's desktop until it says
'Added XX' or 'Removed XX', and you're done!

Hope this helps!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, December 15, 2011 3:37 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] libraries, References and some subtleties to be
considered

I use libraries - MDAs - to hold common code, variables and constants.
Libraries are essentially places to put common code so that many different
applications can do things the same way.  If a bug is found it can be fixed
in the library, in just one place.

It is possible for a lib to reference another lib.  For example my C2DbFW3G
understands and uses my Presentation Level Security System and so it
references C2DbPLSS.  However C2DbPLSS is a standalone library, i.e. it can
be used without my FW3G.  Should I have just merged the two into one big
lib? 
That is a conversation for another day.

While on this subject, two more things.  There can be no circular references
between libs, i.e. FW3G cannot reference PLSS *and* PLSS also reference
FW3G.  Any lib can reference another lib but the reference can never "circle
back around".  Additionally the order of reference comes into play if there
are two functions, classes, variables etc with the same name.  We all
understand the scope thing (local function, module, global) but the same
issue exists in libraries in that if a name is not found in the local
container the compiler starts looking at other referenced objects, starting
from the top reference in the references dialog and working down.

This can cause oddities if we have a function (for example) with the same
name found in the application and the library.  Code in the application will
use the function inside of the application container, whereas code in the
library will use the function in the library container. 
If you use libraries and you write a function and move it to the library, do
not forget to delete the function from the application or you will have
problems.

I have two main libraries, C2DbFW3G which is the 3rd generation of my
framework, and C2DbPLSS which is my Presentation Level Security System.

Having an application reference a library causes some issues shall we say
which do not exist if you do not use them, and I just thought I would walk
through my findings and how I handle things in order to start a conversation
on the subject.

Some tidbits in no particular order.

When the developer references a library they do so via a browse button and
so the reference ends up specific to a location available from the
developer's machine.  This implies that the location may or may not be
available to another user opening the application.

When the application opens, it tries to find the file at the location
specified in the existing reference.  If found it uses that copy of the
library, no questions asked.

If the library cannot be found at that referenced location then the
application silently begins to search a set of paths to find the library.

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

If the library is found the search immediately ceases and the reference is
"fixed up" to point to that location.  When the application closes it saves
that new reference location.  So the application has been silently
"re-referenced" to the new location.  When I say silently, I mean that there
is no immediate in-your-face indication that any of this happened.

This silent re-reference can cause odd problems.  Let's take some real life
scenarios that I encounter at my client.

I have a directory on my C: drive at the client called C:\Dev\DisNew\  This
path, in particular the Dev\ part, is unique to my machine (standing for
development).  I build a framework and an application in this location.  I
reference the lib from the application, browsing to that location and voila,
the reference points to a library in a location that does not physically
exist anywhere else in the company.

I copy the two files up to X:\DisNew\Test which is the production
(X:\DisNew) test directory.  the user has a batch file which builds a
directory on their local C: drive, copies the library and application to
that local directory and opens the application.  The application tries to
find the lib at my dev directory and fails, so it tries to find it in the
local directory and succeeds.  Life is good.

Now... I go into the X:\Disnew\Tester directory and open the application
file.  Guess what happens? 
  The application opens and tests the reference and... finds it because it
can see my dev path.  The file works.  Life is good, nothing changes.

A user goes into the X:\DisNew\Tester directory and opens the application
file and ... the application cannot find my dev directory so it starts "the
search".  It finds the library in the X:\DisNew\Test directory and
re-references and the application works.  Now when the user closes the
file... the file is referenced to the lib on the network.  Life is no longer
good!

Now we decide that the application file tests good and copy it to production
where it is copied, along with the lib down to the user's hard disk.  The
user opens the copy on their hard disk and... 
the application is referenced to the lib on the network (test directory) and
so it opens the lib on the network.  Now I am trying to copy a new version
of the lib to tester and the file is locked.  Or something.  Life is not
good.

Let's discuss decompile for a minute.  Decompile flushes the pcode buffers
in the Access container, which, simply put, means that all of the "compiled"
code is flushed out.  Yes I understand that Access is an interpreter but it
actually compiles the English (VBA) language stuff we write into P-Code and
interprets the P-Code.  The compile of the Decompile / Compile matched pair
simply recompiles every single line of VBA code into P-Code and stuffs it
back into the buffers.

When you perform a decompile / compile, you *REALLY* need to decompile /
compile the library first, then the application using the application.  I
don't understand all of the stuff but apparently there is a table of
pointers built by the compile, things like the entry point to functions and
the locations of constant and variables.  Apparently when you compile the
application, it goes out and searches the library for these tables in order
to correctly call functions and variables in the library.

But why do we do a decompile / compile in the first place?  Because it is
possible and in fact not uncommon, for the P-Code to get corrupted over
time.  If the lib is corrupted and you recompile the app, then the app calls
into corrupted lib stuff.  So, decompile / compile the lib *before* you
decompile the application that references the lib.  And if you decompile /
compile the lib, then you must must *must* recompile the app because the lib
entry points and variables might change.

Guess what?  If you happen to get confused and decompile / compile anything
on a network share... it may (or may not) cause weird things like the app
refusing to close.  So never never *never* decompile / compile anything that
is not local to your hard disk.

Unfortunately the simple fact that FW3G references the PLSS does not expose
the PLSS on through to the application.  So C2DbFW3G references C2DbPLSS and
the application references C2DbPLSS *and* C2DbFW3G because it directly uses
code in both.  Oh my goodness.  Now I have to decompile / compile the PLSS
first, then the FW3G (because it references PLSS), and then the application
(which directly references both libs).  All of this must be done on my local
machine so as to avoid the "can't close" issue discussed above, and then
copied to the final destination for public consumption.

Furthermore I need to make sure that I reference the PLSS in the FW3G to the
DEV path on my local machine, and likewise reference PLSS and FW3G inside of
the application to the dev path of my local machine.  Why?  Because that
path is not public to the company and will trigger the re-reference when the
user downloads all this stuff to their local machine.

But wait, there's more.  I have three different applications that use the
PLSS and the framework. 
So if I decompile / compile the PLSS / FW3G, all of the applications that
use these libs need to be recompiled.  Again, if I make changes to the libs,
any app that I do not decompile will not reacquire the pointer tables in the
libs and may start to fail.


And around and around we go.

I use batch files to copy these pieces to the user's system so that the user
ends up with local copies and doesn't end up permanently re-referencing
things back to the production location.  This works reasonably well as long
as everyone plays by the rules.  If anyone (other than myself) actually
opens any of these files up in tester or production, then the references
silently change and things go south in a hurry.  It took me awhile to figure
out that this was happening (a long time ago) and it took me awhile to
remember that this occurs when I started having strange things happening
recently.  That is the reason for starting this thread, to remind the list
how this stuff works and to get input from other list members on their
experiences with this stuff.

I am a believer in libraries to hold common code.  They exist for the simple
reason that changes to that code, bug fixes etc can be done in one place and
propagated to every place the change is needed.  It is important to
understand what goes on behind the scenes however or you can have some
strange things happening that will be very difficult to figure out.

--
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list