Access Application that Checks for New Version

[adversus]

Good afternoon folks:

I am maintaining an Access application (in the form of a compiled ADP project, *.ade) that currently gets installed on client machines via a Windows VBScript. Right now when I push an update, I compile the project into the install folder on the network and Email all my users with the script attached. The users open the script and it installs a local copy from the network onto their desktop.

Now, I'm curious if there is a way to have Access automatically check every few minutes to see if there is a new version of the main ADE file on the network, and if there is, to throw up a message box to the user telling them to re-install. Ideally the process would look like this:

1. Updated ADE file is saved to the network
2. Every 2 minutes the clients check the network file and see if the version number updated
3. If version number updated, display message to user telling them a new version is available, with two options:
-Install Now
-Install Later
4. If Install now is selected, Access exists and the install script I already have written is ran
5. If Install Later is selected, Access runs and the message will re-appear during the next scheduled version check

I've done something similiar using Java and C++, but I'm not sure if Access/VBA can accomodate this kind of automated version checking :) Any insight or tips would be appreciated!

[ADezii]

First and foremost, this code is right off the top of my head and has no guarantee whatsoever. Initial testing did prove successful, but it hasn't been tested extensively, and I really do not have the time. Here goes:

1. On your Main Form set the Timer Interval to 120000. This Property is calibrated in milliseconds, so 120000 would be equivalent to your requested 2 minutes. Do not close this Form since that will disable the Timer, you can Minimize it or make it invisible, but do not close it.
2. Copy and Paste the following code to the Form's Timer() Event, it will be executed every 2 minutes. This code will:
   

   Dim appAccess As Access.Application
   Dim varProjectVersion As Variant
   Dim varLocalVersion As Variant
   Const conOPEN_EXCLUSIVE As Boolean = False
    
   '************* Interrogate the Access Project for its Version *************
   'Initialize String to Patabase Path
   Const conPATH_TO_PROJECT = "C:\_TheScripts\_Active Projects\NorthWindCS.adp"
    
   Set appAccess = CreateObject("Access.Application")
    
   'Open Project in Microsoft Access window.
   appAccess.OpenAccessProject conPATH_TO_PROJECT, False
    
   'Retrieve the Project Version via Automation and store in Variable
   varProjectVersion = appAccess.SysCmd(acSysCmdAccessVer)
    
   appAccess.Quit
   Set appAccess = Nothing
   '******* All done as far as the Project on the Server is concerned ********
    
   '***************** Now, let's retrieve the Local Version ******************
   varLocalVersion = Application.SysCmd(acSysCmdAccessVer)
    
   'For testing purposes only
   Debug.Print "Server Version: " & varProjectVersion & _
               " <==> Local Version: " & varLocalVersion
    
   'Let's compare both the Local and Server Versions
   If varLocalVersion < varProjectVersion Then
     MsgBox "The Local Version is less than the Server Version, Upgrade time!", _
             vbExclamation, "Local Version < Server Version"
   ElseIf varLocalVersion = varProjectVersion Then
     MsgBox "Versions on both the Server and Client are the same!", _
             vbInformation, "Versions Same"
   Else    'Local Version greater than the Server Version, should never happen?
     MsgBox "Time to Upgrade the Server Version", vbExclamation, "Upgrade Server"
   End If
    
   'At some point do you wish ti disable the Timer?
   'Me.TimerInterval = 0

   1. Open the Access Project on the Server (substitute your Path) within the context of your Access Window making it the Current Database. The Project is opened via Automation and the OpenAccessProje ct() Method.
   2. Once you have opened the Access Project and set an Object Variable to Reference it, you can retrieve its Version Number.
   3. Close the Automation Session.
   4. Retrieve the Current Database Version.
   5. Make comparisons between the two and make decisions.
   6. This code will be executed every 2 minutes.
   7. That's it. Any questions, please feel free to ask.
3. Good Luck and Good Night!

[missinglinq]

ADezii's code is usually spot on, so I expect it'll do the job, but I've got to tell you, checking for front end updates every few minutes is a gross case of overkill, and will chew up system resources! Although I've never had any problems, the literature has a number of reports of the continuous use of the Timer causing graphics problems, such as controls intermittently flickering/flashing. The usual strategy that is used by developers who do frequent front end updates is to check for updates either each time the database is opened or the first time it's opened for the day.

I've always thought that users should be notified of any changes that have been made, unless the changes were totally seamless and invisible as far the end users were concerned.

Welcome to Bytes!

Linq ;0)>
.

[ADezii]

ADezii's code is usually spot on, so I expect it'll do the job, but I've got to tell you, checking for front end updates every few minutes is a gross case of overkill, and will chew up system resources! Although I've never had any problems, the literature has a number of reports of the continuous use of the Timer causing graphics problems, such as controls intermittently flickering/flashing. The usual strategy that is used by developers who do frequent front end updates is to check for updates either each time the database is opened or the first time it's opened for the day.

I've always thought that users should be notified of any changes that have been made, unless the changes were totally seamless and invisible as far the end users were concerned.

Welcome to Bytes!

Linq ;0)>
.

Excellent points, Linq. My last question to the OP, which I forgot to ask, is why the 2 minute Interval?

[adversus]

The application in question is used for the processing of a large scale data migration and merge project. Due to the ever changing nature of the data requirements, it sometimes become necessary to make several modifications in a single day.

After thinking on it, I agree the quicker checks is overkill. I'll probably change it to every 20-30 minutes and see what thats like. And if it is a critical update, I can push it out the current way (via email and an install script).

Thanks for the direction, I'll take a look at this and see if it'll work for my scenario :)

[missinglinq]

Sounds like a challenging project!

Linq ;0)>

[ADezii]

The application in question is used for the processing of a large scale data migration and merge project. Due to the ever changing nature of the data requirements, it sometimes become necessary to make several modifications in a single day.

After thinking on it, I agree the quicker checks is overkill. I'll probably change it to every 20-30 minutes and see what thats like. And if it is a critical update, I can push it out the current way (via email and an install script).

Thanks for the direction, I'll take a look at this and see if it'll work for my scenario :)

Don't forget that if you are setting the Timer Interval for 20 or 30 Minutes, the values would be 1200000 and 1800000 respectively.