Miscellaneous Deployment Scenarios
Once the application has been deployed and users begin to use it in the real world, there still may be work to be done. For example, what happens if you have a split database and the back-end database moves? Or, if you want to publish an update to your application, how do you verify that users always have the latest version? We'll look at ways you can accomplish these tasks in your applications to help prevent failures from occurring and keep applications up-to-date.
Updating Your Applications
Keeping an application up-to-date can also be a challenge. If you're writing applications within an organization, you may have to coordinate with everyone to close the database. If you're writing an offthe- shelf application, your customers may be spread over a wide distance making it even more difficult. Here are a few things you should think about to keep an application up-to-date.
Checking for Latest Version
Earlier we defined a version number that we could stamp in our applications. This version number can then be used to determine when a newer version of the application is available. In order to detect an update, you'll need some other piece of information available, typically in a central location. The solution will contain two databases. The first is called VersioningServer.accdb and will contain the application history including the version numbers, as well as a class module that is responsible for doing most of the work. The second database is the actual client application, which for the purposes of demonstration, we've simply named ClientApp.accdb.
Updates may be best served when the data in the database is linked. Before you rename the previous database, be sure there is a backup!
Create the Versioning Server
Begin by creating VersioningServer.accdb. In this database, create a new table with the fields shown in the following table.
|Field Name||Data Type (Size)|
|ID||AutoNumber (Primary Key)|
Save this table with the name USysAppHistory. Enter one record as shown in the table that follows.
Be sure to change the path to the ClientApp.accdb on your computer. This path should represent a copy of the ClientApp.accdb file that we'll create later.
Next, create a new form in the versioning server called frmTimer. Add the following code to the Timer event for the form. The Timer event is used to give the original client application enough time to close. This event will start the actual updating of the application.
Private Sub Form_Timer() ' turn off the timer Me.TimerInterval = 0 ' kick off the actual update m_updater.Update End Sub
Next, create a new class module called Updater. This class will be responsible for doing the bulk of the work. Add the following declaration code to the class. Notice that for simplicity we're using a Public variable called ApplicationDatabase in place of a Property procedure.
Private Const TIMER_INTERVAL As Long = 3000 Private Const IDX_MAJOR As Integer = 0 Private Const IDX_MINOR As Integer = 1 Private Const IDX_BUILD As Integer = 2 Private Const IDX_REVISION As Integer = 3 Public ApplicationDatabase As String
Next, add a new method to the class called StartUpdate. This method will open the frmTimer form and set the TimerInterval property of the form.
Public Sub StartUpdate() ' open the timer form DoCmd.OpenForm "frmTimer" ' sets the timer that will start the update Forms("frmTimer").TimerInterval = TIMER_INTERVAL End Sub
To determine whether a client is running the latest version of the application, we'll create a method in the server application called IsLatestVersion. Declare the method as follows:
Public Function IsLatestVersion() As Boolean Dim v1 As String Dim v2 As String Dim stSQL As String Dim db As DAO.Database Dim rs As DAO.Recordset2
Make sure that the ApplicationDatabase property has been set and is valid. If it has not been set or does not exist, we'll assume that the client is running the latest version and return True. This is to prevent any update later on.
' validate the ApplicationDatabase If (Len(ApplicationDatabase) = 0 Or Dir(ApplicationDatabase) = "") Then MsgBox "ApplicationDatabase property not set", vbExclamation ' assume the database is current so we don't try to update it later IsLatestVersion = True Exit Function End If
It's time to start retrieving the versions of the applications. We'll start with the client application. The version information has been stored in a custom database property called AppVersion. This is similar to the solution used earlier in this tutorial. To retrieve this property, we'll open the database using DAO, and then call a helper function called GetVersionOfDatabase, which will be defined shortly.
' get the version of the application database Set db = DBEngine.OpenDatabase(ApplicationDatabase, False, True, "") v1 = GetVersionOfDatabase(db) db.Close Set db = Nothing
Next, we need to retrieve the latest version number from the USysAppHistory table. To do this, we'll open a recordset against this table that retrieves the version number for the record with the highest ID number:
' get the latest version of the database defined in USysAppHistory stSQL = "SELECT VersionNumber FROM USysAppHistory " & _ "WHERE ID=(SELECT Max([ID]) FROM USysAppHistory)" Set rs = CurrentDb().OpenRecordset(stSQL) v2 = rs("VersionNumber") rs.Close Set rs = Nothing
Last, we'll call a helper function called CompareVersions that compares the two version strings to determine which one is greater.
' return IsLatestVersion = (CompareVersions(v1, v2) <= 0) End Function
Before we write the code that does the update, let's take a look at the two helper functions we declared, beginning with GetVersionOfDatabase:
Private Function GetVersionOfDatabase(db As DAO.Database) As String GetVersionOfDatabase = db.Containers("Databases").Documents("UserDefined").Properties("AppVersion").Value End Function
The other helper function we declared is CompareVersions. The design of this function is modeled after the Compare method of the String object in the .NET Framework. The function returns -1 if the first version is greater than the second version, 1 if the second version is greater than the first version, and 0 if they are equal.
Private Function CompareVersions(ByVal stVersion1 As String, _ ByVal stVersion2 As String) As Integer ' parse the version strings Dim astVersion1() As String Dim astVersion2() As String Dim iCnt As Integer
To ensure that version numbers are always in a consistent format, we'll call the helper function NormalizeVersionString. This function formats a version number in the format: major.minor.build .revision. If the build and revision parameters are not supplied, the function fills in zeros:
' normalize the strings into ' major.minor.build.revision stVersion1 = NormalizeVersionString(stVersion1) stVersion2 = NormalizeVersionString(stVersion2)
Split the strings using a period as the delimiter. The Split function returns an array for each item of a string given a delimiter.
' split the strings astVersion1 = Split(stVersion1, ".") astVersion2 = Split(stVersion2, ".")
Next, loop through each element of the arrays and compare the array for the first version to the array of the second version:
For iCnt = IDX_MAJOR To IDX_REVISION ' validate - make sure there is an index If (UBound(astVersion1) >= iCnt And UBound(astVersion2) >= iCnt) Then If (CLng(astVersion1(iCnt)) > CLng(astVersion2(iCnt))) Then ' stVersion1 > stVersion2 CompareVersions = -1 Exit Function ElseIf (CLng(astVersion1(iCnt)) < CLng(astVersion2(iCnt))) Then ' stVersion2 > stVersion1 CompareVersions = 1 Exit Function Else ' equal: do not exit because we need to make it all the way through CompareVersions = 0 End If End If Next End Function
Add the following code for the NormalizeVersionString function:
Private Function NormalizeVersionString(stVersion As String) As String ' parse the version strings Dim a() As String a = Split(stVersion, ".") ' normalize the strings: ' major.minor.build.revision If (UBound(a) = IDX_MINOR) Then stVersion = stVersion & ".0.0" ElseIf (UBound(a) = IDX_BUILD) Then stVersion = stVersion & ".0" End If NormalizeVersionString = stVersion End Function
With those functions out of the way, we can begin to see how the updates are actually done. Begin by declaring a new method called Update.
Public Sub Update() Dim obj As Access.Application Dim rs As DAO.Recordset2 Dim stBackup As String Dim stSQL As String
Earlier, we defined a method called StartUpdate, which opens the frmTimer form. If this form was not opened, we know that the StartUpdate method was not called. Make sure the form is open and display an error otherwise.
' make sure that the Timer form is open ' used to make sure that StartUpdate has been called If (Not CurrentProject.AllForms("frmTimer").IsLoaded) Then MsgBox "Cannot call Update method without StartUpdate method", _ vbExclamation Exit Sub End If
It is the responsibility of the client application to close itself if it is not running the latest version. To make sure that the file has been closed, we'll loop until the LDB file for the client application has been deleted and no longer exists:
' wait for the file to close Do DoEvents Loop Until (Dir(LDBOfDB(ApplicationDatabase)) = "")
Next, we'll make a rudimentary backup of the client application by appending a .old file extension to it. This deletes an existing file with this name before making a backup:
' delete the existing database that was backed up as .old stBackup = ApplicationDatabase & ".old" If (Dir(stBackup) <> "") Then Kill stBackup End If ' rename the existing database Name ApplicationDatabase As stBackup
The path to the updated version of the file is stored in the USysAppHistory table. Add the following code to copy this file to the path defined by the ApplicationDatabase property.
' copy the new file stSQL = "SELECT VersionNumber, FilePath FROM USysAppHistory " & _ "WHERE ID=(SELECT Max([ID]) FROM USysAppHistory)" Set rs = CurrentDb().OpenRecordset(stSQL) FileCopy rs("FilePath"), ApplicationDatabase rs.Close Set rs = Nothing
Almost done. We need to start the client application. To do this, we'll automate a new instance of Access and set the UserControl property to True so the user can interact with it:
' start the new instance If (Dir(ApplicationDatabase) <> "") Then Set obj = New Access.Application obj.AutomationSecurity = 1 ' Low obj.UserControl = True obj.OpenCurrentDatabase ApplicationDatabase End If
Last, after the new instance has been launched, close the versioning server database:
' cleanup Application.Quit acQuitSaveNone End Sub
We defined a helper function in this method called LDBOfDB, which returns the name of an .ldb or .laccdb file for a given database.
' Gets the LDB file name for a given database Private Function LDBOfDB(stDb As String) As String Dim stExt As String stExt = Mid(stDb, InStrRev(stDb, ".") + 1) If (stExt Like "md*") Then LDBOfDB = Left(stDb, InStrRev(stDb, ".") - 1) & ".ldb" ElseIf (stExt Like "accd*") Then LDBOfDB = Left(stDb, InStrRev(stDb, ".") - 1) & ".laccdb" End If End Function
To return an instance of this class to the client application, we need to create a routine that returns an instance of it. Create a new standard module called basUpdater with the following code.
Public m_updater As Updater Public Function GetUpdaterInstance() As Updater If (m_updater Is Nothing) Then Set m_updater = New Updater End If Set GetUpdaterInstance = m_updater End Function
Because class modules are Private by default, this code will fail when you call it from the client application. This is because the client application won't be able to create an instance of the Updater class. To solve this problem, set the Instancing property of the Updater class to PublicNotCreatable. The GetUpdaterInstance function in the versioning server is responsible for creating an instance of the class but it needs to be visible to the client application.
In this tutorial:
- Creating an Automated Build
- Design the Form
- Retrieving Information from the Source Database
- Building the Application
- Creating the Target Database
- Set Build Properties on the Target Database
- Setting Build Properties on the Source Database
- Deleting Data from Tables
- Calculating the Version Number
- Handling Application Dependencies
- Updating References
- Testing Reference Fix-Up
- Late Binding
- Licensing Your Applications
- Number of Records
- Restricting the Number of Times an Application is Launched
- Registering an Application
- Creating a Registration Web Service
- Validate the Registration Using the Web Service
- Miscellaneous Deployment Scenarios
- Create the Client Application
- Testing the Versioning Server
- Re-Linking Tables Automatically
- Programmatically Creating DSNs
- Creating a User DSN
- Ensuring an Application Runs Locally