Microsoft Access Database Autoupdate

or

"How can MS Access update the clients with the latest front-end database I put on the server automatically?"

Consider the situation where you have developed a Microsoft Access application, and have it installed with a user base in an office of more than a few people. If you have a new version, going to each machine to upgrade them all each time is a pain.

In the old days (pre-2000) you could have the data and front end databases both located on a file share on a server and all would be good. You could even develop the code while people were using it! All the users needed was a shortcut to the database and this never changed. Very convenient.

However, since 2000 "progress" was made, and the recommended solution was to have a copy of the front end database local to each user. If you didn't follow this friendly advice from Microsoft, the front end database would get corrupted very quickly, which as they say in the classics, was bad. The actual tables (data) are still located centrally and linked in, along with any SQL server tables etc. These didn't corrupt despite being shared, or at least, not as often. Anyway, this setup presented a problem for the developers - how to now roll out a new version of the application.

Like most problems, there are several possible solutions. The one I discuss here is one I created and used in production for a while, and it worked quite well for us. The only slight trick is having two version numbers - one in the local code, and one in a table. Here is the setup and a rough guide to the steps without the full code --

General Setup

• Create a table called "tblVersion" with a field called "Version" of type text.
• Have one record with the version number, such as "1.0.0" to start off, in this "Version" field.
• In the main form of the application, create a constant in the code behind called constTHIS_VERSION, with the value set to "1.0.0" again also.
• In the same main form, have an event somewhere (onOpen) that checks the version constant versus the version in the table. 
• If they are different (and you could check more recent, but I like the idea of being able to rollback) then you make the upgrade button visible and warn the user with a dialogue box that a new version is ready to be upgraded to. You could bypass some of this and just make it fully automated, but I like giving the users the control on when to do the upgrade myself.
• If the upgrade button is pressed, you call the OS to run a BAT file and exit the Access application immediately.
• The BAT file pauses for a few seconds (to allow for the quit), then copies the front end database from the known server location to the local C: drive location.
• When the copy is done, it calls MS Access with the database as a parameter to re-launch the database. Note that different versions of Access have different paths to the executable, and different OS's also (eg Win 7). There is a one-off edit of this BAT file per machine to make sure this is pointing to the right place. If you have a SOE (Standard Operating Environment) then this is a non-issue.

Upgrade Steps

So when you have a new version of your database, what do you need to do to roll that baby out?

• Edit the main form in the new version and change the constant to a new version number (eg "1.0.1")
• Copy the new version to the server location with the standard name, eg "myDB.mdb"
• Edit the database table "tblVersion", and change the version number (eg "1.0.1")

Code


So there are two main bits of code to consider here. One is the checking of the version numbers and launching of the BAT file in Access, and the other is the BAT file contents. Both are pretty simple when it comes down to it.

Code in Access

First, the code behind the events. I bound the form to the version table, and had the field "txtVersionFromTable" in an invisible textbox.You could use a dlookup instead.

Const constTHIS_VERSION = "1.0.0"

Private Sub Form_Activate()    
    DoCmd.Maximize        
    ' Set the string on the form, so users can see what version they have.    
    txtVersion.Caption = "Version " & constTHIS_VERSION  
    'Check version    
    If [txtVersionFromTable] <> constTHIS_VERSION Then        
        MsgBox "Version " & [txtVersionFromTable] & " is ready for download. Please update ASAP (by pressing upgrade button)!" ' This doesn't cause an infinite activation loop.        
        cmdUpgrade.Visible = True 'Show upgrade button    
    Else        
        cmdUpgrade.Visible = False    
    End If
End Sub

Private Sub cmdUpgrade_Click()    
    ' They pressed the upgrade button.    
    ' This code upgrades the LOCAL copy of the database from the server.    
    Dim retval    
    retval = Shell("C:\LocalDBFolder\upgradeDB.bat", 1)     
    ' This will pause to allow us some time to exit    
    Application.Quit    ' Get out now!!!
End Sub


Code in Batch file

Now the BAT file contents. You can copy/paste this code directly into a notepad file and save it as the "c:\LocalDBFolder\upgradeDB.BAT" file. It assumes F: drive is shared, but you can change that to whatever or even use \\servername\sharename instead.

echo off
cls


echo Waiting for Access to close...
sleep 8


echo Copying new version from server...
xcopy "F:\ServerDBFolder\myDB.mdb" "c:\LocalDBFolder" /Y


echo Relaunching Database..
sleep 8

rem "path\file to Access exe" "path\file to mdb"
rem You may need to check this line for different OS / Office versions


"C:\Program Files (x86)\Microsoft Office\OFFICE12\MSACCESS.EXE" "c:\LocalDBFolder\myDB.mdb"

Done and Done.

That's it! Easy, right?

One nice thing about this technique is that you can handle different versions of Microsoft Access in your client base. If you stick your front-end to "2000 format", then it can be used by 2000, 2003, 2007 and 2010 clients. The only thing to watch for is that re-launch line in the BAT file will need to be right. Same with the OSes - XP, Vista or Win7 all work with this.

If this technique helped you, please leave a comment!

EDIT : Tuesday 30th August : SLEEP command

Ah, I forgot that the SLEEP command in the batch file may not be installed. However, it is a free download as part of the Microsoft Server 2003 resource kit. Don't be alarmed, it will work on pretty much anything - XP, Vista, Win7 etc. Download it here. Copy the SLEEP.EXE to the client somewhere in it's path, such as the C:\Windows\System32 (for XP). Open a DOS window and type in SLEEP 10 to make sure it's working. For more on the SLEEP command, try here.