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.
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:
Option Compare Database Option Explicit Private strVerClient As String Private strVerServer As String
Private Sub Form_Load() On Error Resume Next ' Populate module level variables when form loads. strVerClient = Nz(DLookup("[VersionNumber]", "[tblVersionClient]"), "") strVerServer = Nz(DLookup("[VersionNumber]", "[tblVersionServer]"), "") End Sub
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.
Option Compare Database Option Explicit Dim strPath As String Dim strDest As String Dim strBkup As String Dim strMyDB As String Dim strVer As String
Private Sub Form_Open(Cancel As Integer) On Error Resume Next ' Update status form to identify version being copied. strVer = DLookup("[VersionNumber]", "tblVersionServer") Me.txtVer.Caption = "Installing version number ... " & strVer ' Load variables with correct file name-path values. strMyDB = CurrentDb.Name strPath = Left(strMyDB, LastInStr(strMyDB, "\")) strDest = Replace(strPath, "Resources\", "ClientDB.mdb") strBkup = Replace(strPath, "Resources\", "ClientDB_bkup.mdb") ' Stop processing here so article readers may step through code. Stop ' Create a backup (replacing existing backup if necessary) and ' remove the target file. If Dir(strBkup) <> "" Then Kill strBkup FileCopy strDest, strBkup If Dir(strDest) <> "" Then Kill strDest End Sub
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
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.
REM This batch file copies the XYZ client from \\xyz001\ server REM ... and starts the program. COPY \\xyz001\XYZClient.mdb c:\XYZ\XYZClient.mdb START "MSAccess.exe" c:\XYZ\XYZClient.mdb EXIT
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