By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,067 Members | 1,849 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,067 IT Pros & Developers. It's quick & easy.

Access Application that Checks for New Version

P: 14
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!
Jul 30 '08 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,627
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:
    Expand|Select|Wrap|Line Numbers
    1. Dim appAccess As Access.Application
    2. Dim varProjectVersion As Variant
    3. Dim varLocalVersion As Variant
    4. Const conOPEN_EXCLUSIVE As Boolean = False
    5.  
    6. '************* Interrogate the Access Project for its Version *************
    7. 'Initialize String to Patabase Path
    8. Const conPATH_TO_PROJECT = "C:\_TheScripts\_Active Projects\NorthWindCS.adp"
    9.  
    10. Set appAccess = CreateObject("Access.Application")
    11.  
    12. 'Open Project in Microsoft Access window.
    13. appAccess.OpenAccessProject conPATH_TO_PROJECT, False
    14.  
    15. 'Retrieve the Project Version via Automation and store in Variable
    16. varProjectVersion = appAccess.SysCmd(acSysCmdAccessVer)
    17.  
    18. appAccess.Quit
    19. Set appAccess = Nothing
    20. '******* All done as far as the Project on the Server is concerned ********
    21.  
    22. '***************** Now, let's retrieve the Local Version ******************
    23. varLocalVersion = Application.SysCmd(acSysCmdAccessVer)
    24.  
    25. 'For testing purposes only
    26. Debug.Print "Server Version: " & varProjectVersion & _
    27.             " <==> Local Version: " & varLocalVersion
    28.  
    29. 'Let's compare both the Local and Server Versions
    30. If varLocalVersion < varProjectVersion Then
    31.   MsgBox "The Local Version is less than the Server Version, Upgrade time!", _
    32.           vbExclamation, "Local Version < Server Version"
    33. ElseIf varLocalVersion = varProjectVersion Then
    34.   MsgBox "Versions on both the Server and Client are the same!", _
    35.           vbInformation, "Versions Same"
    36. Else    'Local Version greater than the Server Version, should never happen?
    37.   MsgBox "Time to Upgrade the Server Version", vbExclamation, "Upgrade Server"
    38. End If
    39.  
    40. 'At some point do you wish ti disable the Timer?
    41. '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 OpenAccessProject() 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!
Jul 31 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
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)>
.
Jul 31 '08 #3

ADezii
Expert 5K+
P: 8,627
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?
Jul 31 '08 #4

P: 14
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 :)
Jul 31 '08 #5

missinglinq
Expert 2.5K+
P: 3,532
Sounds like a challenging project!

Linq ;0)>
Jul 31 '08 #6

ADezii
Expert 5K+
P: 8,627
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.
Jul 31 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.