Switched Keys

A question came up on one of the discussion forums is how to automatically update the front end (FE) of a split database. Instead of taking up space on the board, I'm posting it here. This is the method I used on the only situation where I provided that feature.

Overview

I use the opening screens of the front end (FE) and a separate database that does the updating. The intermediate database is used because it is small while the updated version may be large, slow, etc., and enables access to make a backup of the existing FE. This is a summary, with the relevant code following.

1. After opening the splash screen of the FE, I open a version check form that compares the versions. The recordsource of the form gets version Ids from FE and back end (BE), using equality because versions may have alphabetical suffixes. This is used in the form_open code.

   SELECT tblBEVersion.VersionID, tblBEVersion.BEVersionNumber, tblFEVersion.VersionID, tblFEVersion.FEVersionNumber
   FROM tblBEVersion INNER JOIN tblFEVersion ON tblBEVersion.[VersionID] = tblFEVersion.[VersionID];

2. If there is a difference, the versioncheck form asks if the user wants to update. Some users may have customized their version of the FE, and not all users may have the same FE. If the update is critical, you could add another field to the BE table and use that instead of the prompt if needed.
3. If approved, the FE copies an updater database to the user system, shells to open it and then closes itself.
4. When the Updater opens, it presents another warning and asks if user is sure they want to update. If not, shell to the existing FE.
5. If approved by the Updater, the Updater backs up the current FE and copies the new version to the user system, and shells to open it. This is a major case when it may  be necessary to reconnect to BE tables.

When developing the updater, you need to set the opening form for the updater. You will want to remember that if you set it up so that the user can not edit the database, you can't get back in to edit it either. You can, however, create a new database and import the objects.

Source Code

This is the relevant code for the Auto Updating system  I use. This was based on some code provided by one or more other developers so it may be familiar. I only used it in one case and as I post this I notice that there is much room for improvement...when I next need to use it. It's also old (possibly 2003) so some functions (FileCopy, Instr) may already exist in current version of Access.

Front End Database

frmSplash (automatically opens)

Private Sub CloseSplash_Click()
On Error GoTo Err_CloseSplash_Click

    Dim stDocName As String
    stDocName = "frmVersionCheck"
    
    DoCmd.OpenForm stDocName
    DoCmd.Close acForm, "frmSplash"

End Sub

frmVersionCheck

RecordSource

SELECT tblBEVersion.VersionID, tblBEVersion.BEVersionNumber, tblFEVersion.VersionID, tblFEVersion.FEVersionNumber
FROM tblBEVersion INNER JOIN tblFEVersion ON tblBEVersion.[VersionID] = tblFEVersion.[VersionID];

Private Sub Form_Open(Cancel As Integer)

Dim strUpdaterPath As String
Dim strCurrentPath As String
Dim strDataFile As String
Me.Visible = False

If FEVersionNumber < BEVersionNumber Then
    
    strMsg = "You do not have the correct version." & vbCrLf & vbCrLf & _
             "Would you like to download the latest client?"
    If MsgBox(strMsg, vbExclamation + vbOKCancel, "Update Client") = vbOK Then
        ' If Updater doesn't exist on Client, then copy it there
        strCurrentPath = Left(CurrentDb.Name, LastInStr(CurrentDb.Name, "\")) & "ExpAuth Updater.mdb"
        
        ' Always copy down newest updater and assume Updater is in the same path as the Data files
        strDataFile = DLookup("Database", "msysobjects", "Name='tblPeople'")
        strUpdaterPath = Left(strDataFile, LastInStr(strDataFile, "\")) & "ExpAuth Updater.mdb"
        FileCopy strUpdaterPath, strCurrentPath
        
        ' Shell to execute/open the updater database
     strUpdateTool = "MSAccess.exe " & """" & strCurrentPath & """"
        Shell strUpdateTool, vbNormalFocus
        DoCmd.Quit
    End If
    
End If

DoCmd.OpenForm "Switchboard"
DoCmd.Close acForm, "frmVersionCheck"

End Sub

Updater Database

frmUpdate (automatically opens)

' In Properties, Timer = 3000

Option Compare Database
Option Explicit

' Set up globals
Dim strPath As String
Dim strDest As String
Dim strBkup As String
Dim strMyDB As String
Dim strVer As String
    
Private Sub Form_Open(Cancel As Integer)

Dim Answer As Byte
On Error Resume Next

Answer = MsgBox("Continue with Menu Update . . .", vbInformation + vbOKCancel, "Menu Update")
If Answer = vbCancel Then DoCmd.Quit
'Form shows Updating text

        DoCmd.Hourglass True
        DoEvents
        
        ' Load variables with correct file name-path values.
        strMyDB = CurrentDb.Name
        strPath = Left(strMyDB, LastInStr(strMyDB, "\"))
        
        ' File specs based on current menu database
        strDest = strPath & "MyExpAuth Menu.mdb"
        strBkup = strPath & "Backups\" & "MyExpAuthMenuUpd_bkup.mdb"
        
        ' Update status form to identify version being copied.
        strVer = DLookup("[BEVersionNumber]", "tblBEVersion")
        Me.txtVer.Caption = "Installing version number ... " & strVer
        
        ' Create a backup (replacing existing backup if necessary) and remove the target file.
        If Dir(strBkup) <> "" Then Kill strBkup
        FileCopy strDest, strBkup

    ' Let Form_Timer take over
       
End Sub

Private Sub Form_Timer()

On Error Resume Next
    Dim strPathOnly As String
    Dim strSource As String
    Dim strHelpFile As String
    Dim strMsg As String
    Dim strOpenClient As String
    Const q As String = """"

    DoCmd.Hourglass True
    DoEvents
    
    Err.Clear
    
    ' We make the assumption that the new client is in the
    ' same folder as this utility.
    
    ' Get path to Data from tblBEVersion
    strPathOnly = DLookup("Database", "msysobjects", "Name='tblBEVersion'")
    strSource = Left(strPathOnly, LastInStr(strPathOnly, "\")) & "MyExpAuth Menu.mdb"
    If (strDest = strSource) Then
        MsgBox "Updater must be run on client machine.  Exiting.", vbCritical, "Error"
        GoTo Cleanup
    End If
    FileCopy strSource, strDest
    
    ' Update help file also
    strHelpFile = Left(strPathOnly, LastInStr(strPathOnly, "\")) & "EAHelp.chm"
    FileCopy strHelpFile, strPath & "EAHelp.chm"
    
    DoEvents
    
    ' Now that the new client file has been copied open new database and close this one.
    strOpenClient = "MSAccess.exe " & q & strDest & q
    Shell strOpenClient, vbNormalFocus

Cleanup:
    ' Cleanup the mouse pointer and exit from this application.
    DoCmd.Hourglass False
    DoCmd.Quit

End Sub