MS-Access / Getting Started

---

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)
VersionNumber	Text (255)
VersionDescription	Text (255)
FilePath	Text (255)

Save this table with the name USysAppHistory. Enter one record as shown in the table that follows.

ID	VersionNumber	VersionDescription	FilePath
1	1.0	Initial release

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.