Featured Database Articles

Automatically Deploy a New Access Client

How do you manage the distribution of new versions of your
Microsoft Access applications to network users? I am sure there are third-party
tools you can buy, but it seems like such a waste. All you want to do is:

figure out if a newer version is available

give the user the option to get latest

copy down the new mdb file from the server

reopen the application

Sounds simple, I know, but if it hasn't already struck you,
there is a little problem with this process: a new version of the
client file cannot be copied over top of the old one while it is in use. The
work-around for this problem requires us to jump around between files, but to
the end user the update will appear seamless. Follow along as we walk through
the process and be sure to download
the code associated with this article.

Getting started

Before we get into the code, we need to take care of some
housekeeping. First, this solution assumes that you have split your data out
from the client and it is stored in a central location on a network server
somewhere. This common network location is very important to the success of
this solution, in as much as its a repository for the following:

1. A
master table with the most current version number.

2. A
master copy of the latest version of your mdb file.

So, let's begin by creating the tables to hold version
numbers. You will need one table in the data file on the server, named
appropriately enough, tblVersionServer and another in the client named
tblVersionClient. The client will link to the version table in the server so
both are available for comparison, as shown in the image below.

Notice in the example that both tables contain only one column and only one
record. In this example, (taken from the download code associated
with the article), the client is version 2.0.6 while the server indicates that
version 2.0.7 is available. When the client application opens, the first order
of business is to compare these two versions to see if it happens to be the
latest. If not, then the process for downloading the latest client is
initiated.

It should be noted that the names and locations of all the files
involved are rather important. Consider the file example below. (While
this is on my C:\ drive, it would normally be placed on the network in a file
share accessible to all.)

For the sake of this article, I named the common network share
"Resources." The sample code specifically looks for a folder with
this name. It also expects to see a data file by the name DataDB.mdb, a client
file by the name ClientDB.mdb and a utility database with the name Update.mdb.

If you unpack the code for this article, the client will automatically relink
to the data, so long as it finds the DataDB.mdb file in a subfolder named
"Resources." While I do not like the idea of hard coding file names
and paths, it can be generally assumed that the location of the data file will
remain constant, so this should not create any kind of hardship. To put this
into production, you will need to modify the code to match your file names and
paths.

The Smart Client

As mentioned above, the client
application contains both version tables: tblVersionClient as a local table and
tblVersionServer as a linked table. Accordingly, it is easy to perform a
comparison of the version values in these two tables when your application
opens. I use a form named frmSplash, which has the company logo and copyright
notice as a start-up form. This form checks table links and version number. The
code looks something like this:

Private Sub Form_Timer()
On Error Resume Next
Dim strMsg As String
Dim strPath As String
Dim strUpdateTool As String
Const q As String * 1 = """"
Me.TimerInterval = 0
' If versions match, then proceed with opening of main form.If strVerClient = strVerServer Then
Me.Visible = False
DoCmd.OpenForm "fmnuMain"
' ... if not, then offer the user the option to download latest.Else
strMsg = "You do not have the correct version." & vbCrLf & vbCrLf & _
"Would you like to download the latest client?"
If MsgBox(strMsg, vbExclamation + vbOKCancel, "Update") = vbOK Then' Notice that I'm using a custom function, LastInStr(), to find
' the last instance of a character. Newer versions of Access expose
' the InstrRev() function to accomplish this, but Access 97 does not.
strPath = Left(CurrentDb.Name, LastInStr(CurrentDb.Name, "\"))
strPath = strPath & "Resources\Update.mdb"
' Enclose the file path in quotes to avoid problems with spaces
' in file and/or folder names.
strUpdateTool = "MSAccess.exe " & q & strPath & q
' This is where the real work happens.
' Use SHELL command to open the UPDATE.MDB utility
' ... then quit this client so it may be overwritten.Shell strUpdateTool, vbNormalFocus
DoCmd.Quit
End If
End If
End Sub

When a version mismatch is found, the user is informed and given the
opportunity to download the latest copy of the client. (Notice in the image
below that the splash screen displays the client version as 2.0.6 and we know
from the discussion above that the server is showing that 2.0.7 is available.)
If the user clicks OK then the ClientDB.mdb file is closed and the Update.mdb
file is opened, but in most cases, this appears to happen within the current
session of Microsoft Access.

Performing the Update

Once the Update.mdb utility opens, it goes right to work.
Once again, the form open event is used to capture file and names and paths
into module level variables. I have also chosen to use this event to make a
backup copy of the user's client ... just in case there is a problem with the
new version. After making a backup, I delete the old client file, and we are
ready for the next step.

In order to allow time for the backup to be created, I place the file copy code
in the form timer event. This little pause is sufficient to ensure that the
backup will have been successfully created and the original client file
removed. From here, it is a simple file copy command and then we use the SHELL
command to reopen our newly copied client. A final DoCmd.Quit will close the
update utility and the user is now presented with the latest client. All of
this is accomplished in the few lines of code below.

Private Sub Form_Timer()
On Error Resume Next
Dim strSource As String
Dim strMsg As String
Dim strOpenClient As String
Const q As String = """"
' We make the assumption that the new client is in the
' same folder as this utility.
strSource = strPath & "ClientDB.mdb"
FileCopy strSource, strDest
' Now that the new client file has been copied, it may
' be opened. Use the SHELL command to accomplish this.
strOpenClient = "MSAccess.exe " & q & strDest & q
Shell strOpenClient, vbNormalFocus
' Exit from this application.DoCmd.Quit
End Sub

Final thoughts

If you download
the code for this article, you will be able to run through the process and
observe the steps more closely. I have added a button to the main form for
resetting the client version back to 2.0.6 and another to force the update
regardless of the version. The code includes a cool function for relinking
tables, which is always useful to have laying around, and that alternate to the
InstrRev() function I use called LastInStr(). (This function, LastInStr() is
not my own code, but has been around forever. My thanks to its author, whoever
that may be.)

I recently got involved in a project where they approach this problem from a
different slant. The developer created a batch file that copies the latest
client from the server to a local folder on the user's machine. The desktop
shortcut the user clicks to launch the program opens NOT the Access mdb file,
but the batch file. Each time the user clicks the link, a new version is copied
down from the server.

While this method is much simpler than the one described
above, it is a hog for network bandwidth and should be considered only where
the client is small and there are few users. I have included it here to show
that I am aware there is more than one approach to this issue. No doubt, many
readers have handled this in different ways and as usual, I look forward to
your feedback.