Distributing an Updated Access Application to a Network


Oh, by the way…


I have a number of clients for whom I have been developing Access applications, based on the principle of “managed specification creep”.  They are constantly coming up with requests for changes, or new functionality that needs to be added.  These applications will never be finished.  I imagine that this may be a familiar scenario to some other freelance developers.


In one case, my client has even established a “Steve To-Do List” spreadsheet, which staff add items to at an average rate of several per month.  Sometimes I am preparing version updates for them several times per week.


For some time, I would get in my car, drive to my client’s office, install the updated front-end file to each of the 7 PCs, and drive home again.  Then I started looking for more efficient ways of doing it.  I know that a lot of others have come up with good solutions to this type of problem.  For example, see http://www.granite.ab.ca/access/autofe.htm for an excellent utility written by Tony Toews.


But in the end, this is how I did it.  For me, it is a suitably simple approach which is applicable to a specific client situation.


Getting the new file to the office


Ok, I have an updated version of a file, let’s call it MyApp.mde, which I need to deliver.  In this case, the first step is for the file to end up in a particular folder on the company’s server.  I could zip it and attach it to an email, and get one of the staff to unzip it into the right place.  Or any of a number of other approaches.  It doesn’t really matter, as long as it gets there, but I obviously prefer to not involve my client’s office staff if possible.  In my example, I have organised to have FTP access to an upload directory on their server – so I upload the file and then use a Remote Desktop connection to move it from the upload directory to where I want it.


Version numbering


This is not commercial software.  It is a custom application for a single organisation.  I just use a simple numbering system to identify versions.  I have a table named VersionRef, with one field, one record, in the frontend MDE file, where I manually enter the new version number before distribution.  So, once my new file is delivered to the client’s server (in this example into the same folder as the application’s backend data file), it obviously has a higher number in the Version field of the VersionRef table than the MDEs currently running on the workstations.


Pilot file


The general concept here is for the existing frontend application files to be replaced by the newer version.  We could get the working copies of the application MDEs to “look at” the MDE on the server to compare version numbers.  However, this is tricky.  What if it finds a newer version?  It can’t replace itself while it is open.


So instead, we have another very simple Access application, which is called Pilot.mde.  The users always launch their database from a desktop shortcut, and this shortcut actually opens the Pilot application.  And this is what it does… crude but effective.  The Startup form, displaying the company logo, has code on its Load event, which compares the version number of the MDE on the server with the version number of the local main application MDE file.  If a newer version is detected, the local MDE is replaced.  Either way, the main application is launched.


Here’s the skeleton of the code:




Private Sub Form_Load()


    Dim rst As DAO.Recordset

    Dim BEPath As String

    Dim ServerFile As String

    Dim ServerVersion As Integer

    Dim LocalFile As String

    Dim LocalVersion As Integer


    ‘ find location of files (see note about assumptions below)

    BEPath = Mid(CurrentDb.TableDefs("UserDefaults").Connect, 11)

    ServerFile = Left(BEPath, Len(BEPath) - 12) & "MyApp.mde"   '12 is specific to this app’s backend MyApp_be.mdb

    LocalFile = CurrentProject.Path & "\MyApp.mde"


    ‘ identify the version numbers of the mde files

    Set rst = CurrentDb.OpenRecordset("SELECT Version FROM VersionRef IN '" & ServerFile & "'", dbOpenSnapshot)

    ServerVersion = rst!Version


    Set rst = CurrentDb.OpenRecordset("SELECT Version FROM VersionRef IN '" & LocalFile & "'", dbOpenSnapshot)

    LocalVersion = rst!Version



    ‘ replace local mde if later version is found on server

    If ServerVersion > LocalVersion Then

        DoCmd.OpenForm "UpdateNotice"

        Kill LocalFile

        FileCopy ServerFile, LocalFile

        DoCmd.Close acForm, "UpdateNotice"

    End If


    Me.TimerInterval = 2000

    Set rst = Nothing


End Sub



Private Sub Form_Timer()


    Dim LocalFile As String

    Dim CmdToOpen As String


    ‘ close Pilot and launch main application

    Me.TimerInterval = 0

    LocalFile = CurrentProject.Path & "\MyApp.mde"

    CmdToOpen = """" & SysCmd(acSysCmdAccessDir) & "Msaccess.exe""" & " """ & LocalFile & """"

    Shell CmdToOpen, vbNormalFocus



End Sub




These assumptions are made in the above code:

-          The Pilot.mde has a link to a table to the main application’s backend data file.  It doesn’t matter which table, so I choose a small and simple one, in the example called UserDefaults.

-          The MDE on the server is in the same folder as the backend data file.

-          Pilot.mde is in the same folder on the workstations as the frontend application MDE.




This is just one way to achieve the purpose of automating the distribution of updated frontend versions to the workstations on a network.  It is a simple solution, and sometimes a simple solution is all that is required.  I have found that it works snappily and smoothly.  I hope this type of approach may prove applicable to others’ circumstances.



Steve Schapel