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
rst.Close
Set rst
= CurrentDb.OpenRecordset("SELECT Version FROM VersionRef
IN '" & LocalFile & "'", dbOpenSnapshot)
LocalVersion
= rst!Version
rst.Close
‘
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
DoCmd.Quit
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.
Summary
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
