How to make sure that everyone has the current version of the frontend and allow them to update.

I had found several FAQ's and Threads relating to this but none seamed to give me a very easy to follow mwthod to do this. After Thinking about it for awhile here is what I came up with.

First let me descride my setup:
- I am not an IT admin so my privliges were limited.
- Win 2k and Win XP machines.
- Access 2000 and Access XP.
- 6 + users of database.
- Database split into a frontend and backend.
- All of the tables are on the backend except for one which is part of the updating process.
- The frontend contains all of the queries, forms, reports, Macros, and modules to minipulate the data.

Now to go into what I did.
First thing I worked on was a way to simply copy the front end to the users desktop (This is ideal for this folder is saved as part of the users roamming profile). I decided on using a batch file, which is stored in the backend folder on the network drive. The coding was simple for this and is as follows:
Code Begins

@echo off
echo ****************************************************
Echo ***** Database Frontend Update *****
echo ****************************************************
Echo This will update the database
echo frontend on your computer.
Pause
g:
Rem the following sets the current dirctory to the directory where the master frontend is located
cd G:\networkfolder\database\frontend
rem Next the copy proceedure launches and copyies the main frontend to the users Desktop directory.

rem The "%USERPROFILE%" is a variable which automatically goes to the current user's profile.
rem You can specify any filename you want. In this example it is "database frontend.mdb"
@echo on
copy database.mdb "%USERPROFILE%\desktop\database frontend.mdb"
@echo off
Pause
exit

Code End

After I got this working the way I wanted it to I needed to devise a way to allow the user to easily update the frontend (Major trial and error here). What I finally came up with was a combination of tables, forms, macros and modules. First I created a simple table to store a number to represent the version number (one field, one entry, no primary key needed). I also created a linked table which is directly linked to the same version table. This allow the distributed front ends to keep an eye on any new versions. I have a main form which is bound to a query. I added the version number table and the linked table to this query and pointed to the number fields of both. I then created text fields ("Current Version" & "Available Version") on the main form which points to this data. Here is an example of what the fields look like.

Current Version: v1.0 ---> shows the local version
Available Version: 1.4 ---> indicates the version available on the server end.


Moving on I created two macros. One calls a module and the other runs the batch file and closes the database. Let me point out the importance of the way the second module is set up and the way the batch file works . The batch file has a pause before it begins the copy process. This is important becuase within the macro it will start the batch file before it closes the database and the pause will prevent any problems from occuring during the copy process. Below is the way both of the macros are setup:

mroDBUpdate1
- RunCode
- Function Name = subUpdateDB ()

mroDBUpdate2
- RunApp
- Absolute path to the batch file
- Quit
- Exit

No lets look at the module called by the first macro.

Public Function subUpdateDB()
Dim strMessage As String
Dim strTitle As String
Dim lResponse As Long
strMessage = "You are about to update your copy of the database frontEnd" & Chr(13) & "The database will close. You will loose all unsaved information." & Chr(13) & "Do you want to continue?"
strTitle = "WARNING!!! Database Is About To Update."
lResponse = MsgBox(strMessage, vbYesNo + vbQuestion, strTitle)
If lResponse = vbYes Then
DoCmd.RunMacro "mroDBUpdate2"
Else
Cancel = True
End If


End Function

The module is simply a way to inteface with the user. I creates a message box which promts the user and asks if they want to continue. If no it cancels the message box and returns to the database. If yes it then calls the "mroDBUpdate2" macro. and that is the extent of the module.

Stay with me for there is more to it.

I need to devise an elegent way of letting the user know there is an update for the frontend available. As I mentioned previously I have a main form which displays all of the time during a users session. Thisform is where this will happen. In the forms On Load event I entered in the follwing code.

Private Sub Form_Load()
If Me.txtCurrVersion >= Me.txtAvailVersion Then
Me.Text22.Visible = False
Else
Dim strMessage As String
Dim strTitle As String
Dim lResponse As Long
strMessage = "There is a new frontend version avaiable" & Chr(13) & "Do you want to update?"
strTitle = "Version Update Alert"
lResponse = MsgBox(strMessage, vbYesNo + vbQuestion, strTitle)
If lResponse = vbYes Then
DoCmd.RunMacro "mroDBUpdate1"
Else
Cancel = True
End If

End If

End Sub

This sub function is an if statement which first sets up a greater then or equel to comparison between the two fields txtCurrVersion & txtAvailVersion mentioned earlier. If the value in txtCurrVersion is greater then or equel to the value of txtAvailVersion then it will just load the form but won't display the txtAvailVersion field. If the txtCurrVersion is less then the txtAvailVersion it will start call a message box letting the user know that there is a new update available and ask them if they want to update now. If yes it will run the macro "mroDBUpdate1". If they choose not to update at this time then the main form will load but the txtAvailVersion will be displayed. Also I added some code to the On Click of the txtAvailVersion to run the "mroDBUpdate1" macro to allow a user to run the update latter.

All this sounds complicated but is really kind of simple in nature. No scripting or anything.