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

jwcolby jwcolby at colbyconsulting.com
Thu Dec 15 20:50:39 CST 2011


Thanks for those suggestions.  My methods are crude and I have always wanted to get a little more 
sophisticated.  I will have to spend a little time thinking about the suggestions you discuss.

John W. Colby
Colby Consulting

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

On 12/15/2011 5:40 PM, Dan Waters wrote:
> 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