My Current Version of Front End Updater (1 Viewer)

mjdemaris

Working on it...
Local time
Yesterday, 23:27
Joined
Jul 9, 2015
Messages
380
A good chunk of this code came from Scott L Prince, which is based on code he got from Bob Larson via StackOverflow.com. It has been adapted to fit into any DB I have created so far.

The current table setup goes like this:
• An admin back end that handles the versioning info, file names, folder paths, etc.
• A link to the “FE_Mod_ver” table in the Admin DB.
• A local table: “AppVer_fe” in each front end that just holds the version number

Each DB file name has been reduced down to a three letter prefix, my personal preference.

Each front end that a user needs access to must be copied from the server folder to their local machine, after which they must stop macros and enable content. Each front end will move itself into one local folder, along with any supporting files I need/want to use to help control automation.

A short cut is also created on the user’s desktop, so that they don’t keep deleting the front end itself. There are about 10 seconds of wait time, programmed in the batch files, to copy, delete files, etc., during which many users state there is a problem and delete stuff and try again. Then they attempt to use the front end on the server side, which is not allowed, though somehow a few have found a way to run it anyway (working on that).
There is some code to be used with a SQL Server, but that is still in development.

There are several hard coded variables:
• The local table: “AppVer_fe”
• The linked table: “FE_Mod_fe”
• And the target directory for the front end and associated files: “c:\users\username\cmms”
• And a few batch file names

Something I recently noticed whilst creating a new DB is that the copied front end file from the server is not deleted, as it should be. For instance, I copy it to the desktop and run it, it should copy itself to the “\cmms” folder and delete the desktop file. I’ll be looking into that…

It does create a shortcut icon every time the program is opened, however, which I would like to change that to check for the shortcut first, but if the user renames the shortcut, then that may be problematic.

The reason I am using a dedicated folder and creating shortcuts is that users don’t typically follow directions well, and many don’t know the difference between a shortcut and the actual program file.

Below is the module code; I am always open to feedback/suggestions. AND – many thanks to the men and women who have helped me along the way, I don’t think I can remember everyone who has had an impact on this module. (I wanted to submit this because of all the help I have received, and to pay it forward.)

Code:
Option Compare Database
Option Explicit

Private m_targetPath As String
Private m_strFrontEndVersion As String            'Front end version number
Private m_strMasterVersion As String              'Master version number
Private m_bTargetFolder As Boolean                'does the target folder exist?
Private m_strMasterPath As String                 'master file path
Private m_strMasterFile As String                 'master file name
Private m_strShortCutBat As String                'name of the short cut file
Private m_bMasterVer As Boolean                   'does a version number exist for this file?
Private m_intModID As Integer                     'module ID
Private m_strCurrApp As String                    'first three letters of file name
Private m_strCurrentPath As String                'Current Project Path
Private m_BatchFile As String                     'BatchFile full path and name

Const c_MasterFETable As String = "FE_Mod_ver"
Const c_AppVerLocal As String = "AppVer_fe"
'Const c_AppVerFE As String = "AppVer_fe_master"
'Const c_AppVerMod As String = "AppVer_Modules"

'=========  SQL Const   ====================
'Const c_MasterFETable As String = "dbo_AppVer_master_Location"
'Const c_AppVerFE As String = "dbo_AppVer_fe_master"
'Const c_AppVerLocal As String = "AppVer_fe"


Public Function CheckFrontEnd()
' ************************************************************
' Created by       : Scott L Prince
' Parameters       : None
' Result           : Determines if backend can be reached, and if front end is the current version.
' Returns          : 0 - Misc Error
'                  : 1 - No current version found in Version Manager file
'                  : 2 - Front end being run from master location
'                  : 3 - Master file path not found in Version Manager file
'                  : 999 - Front end current
' Date             : 5-30-14
' Remarks          : Based on previously-existing code by Bob Larson posted at StackOverflow
' Changes          :
' strFile          : used to send Master FE File to this function
' strDest          : used to send Destination folder to this function
' ************************************************************

'   ========================    NOTES (01/2019)    ========================
'   Passing params to bat and vbs files for shortcut creation. 1/25/2019
'   The AppVer tables have changes, to accomodate the various FE modules.
'   Fields have been added to indicate which FE is where and which to update.


'   =====   Production      =======
    m_targetPath = Environ("userprofile") & "\cmms"
    
    m_strCurrentPath = CurrentProject.path
    
'   =====   Current Dev     =======
'    m_targetPath = "C:\Users\mdemaris\dB_Project2015\ProjectDBs\DevInventory\FE\target"
    
    
Rem     Who am I?
    m_strCurrApp = Left$(CurrentProject.Name, 3)
Rem     What is my ID?
    m_intModID = DLookup("FE_Mod_ID", c_MasterFETable, "s_modPrefix = '" & m_strCurrApp & "'")
Rem     Am I in the Target Folder?
    m_bTargetFolder = CheckTargetDir(CurrentProject.path)
Rem     Does a version number exist for my ID?
    m_bMasterVer = CheckMasterVersion(m_intModID)
Rem     Retrieve the master file name
    m_strMasterFile = DLookup("s_masterFile", c_MasterFETable, "FE_Mod_ID = " & m_intModID)
    
Rem     If CheckTargetDir AND CheckMasterVersion are TRUE, then update if needed
    If m_bTargetFolder = True And m_bMasterVer = True Then
Rem     Which file am I?
        SelectCaseMasterVersion m_intModID
    ElseIf m_bTargetFolder = False And m_bMasterVer = True Then
        m_strMasterPath = DLookup("s_masterPath", c_MasterFETable, "FE_Mod_ID = " & m_intModID)
        
        If ComparePaths(m_strMasterPath, m_strCurrentPath) = True Then
            MsgBox "You are not using the correct file for accessing the Purchase and Requisition Database.  You must copy the original file to your computer and use that one."
            Application.Quit
        End If
        
        Utilities.CreateFolder m_targetPath
        
        CopyMasterFE m_strCurrentPath & "\" & CurrentProject.Name, m_strMasterPath, m_strMasterFile

    End If
    
Rem     Run the bat file that sends mod info and runs shortcut files
Rem     current project path (should be same as target), FE file name, FE mod name
    RunBatFile m_targetPath, m_strMasterFile, m_strCurrApp
    
End Function

Private Function SelectCaseMasterVersion(intMod As Integer) As Integer
Rem     which file am I?
Rem     What do I need to determine this?
    Dim l_BatchFile As String           'This is the cmd file that updates the FE
    
    
    m_strMasterPath = DLookup("s_masterPath", c_MasterFETable, "FE_Mod_ID = " & intMod)
Rem         No Master Path found, return error value.
    If Nz(m_strMasterPath, "") = "" Then
        SelectCaseMasterVersion = 3
    ElseIf ComparePaths(m_strMasterPath, m_strCurrentPath) Then
Rem         The Master File is being ran
        SelectCaseMasterVersion = 2
    Else
Rem         Master path found, and is not being ran.
        m_strFrontEndVersion = DLookup("fe_ver_number", c_AppVerLocal)
        
        Select Case (m_strFrontEndVersion = m_strMasterVersion)
        
            Case True   '   Version numbers match
                SelectCaseMasterVersion = 999
            Case False
                m_strMasterFile = DLookup("s_masterFile", c_MasterFETable, "FE_Mod_ID = " & intMod)
                
Rem                Create the path for the batch file used to update the front end.
                m_BatchFile = m_strCurrentPath & "\UpdateDbFE.cmd"

Rem                Check for an already-existing BatchPath, and kill it if it exists.
                If Dir(m_BatchFile) <> "" Then Kill m_BatchFile

Rem                Notify the user that the application will update.
                MsgBox "UPDATE REQUIRED" & vbCrLf & vbCrLf & _
                    "Your program is not the latest version." & vbCrLf & vbCrLf & _
                    "The front-end needs to be updated. The program will now close and then should reopen automatically.", _
                    vbCritical

                UpdateFrontEnd m_strCurrentPath & "\" & CurrentProject.Name, m_strMasterPath, m_strMasterFile
                
            End Select
    End If
    
End Function

Rem     This function checks if the target dir exists
Private Function CheckTargetDir(currPath As String) As Boolean
    If currPath = m_targetPath Then
        CheckTargetDir = True
        Debug.Print "In Target Dir"
    Else
        CheckTargetDir = False
        Debug.Print "Not in Target Dir"
    End If
    
End Function


Rem     This function checks if a version number is found
Private Function CheckMasterVersion(intMod As Integer) As Boolean
On Error GoTo ErrHandler

Rem     Set mod variable to master version number
    m_strMasterVersion = DLookup("VerNumber", c_MasterFETable, "FE_Mod_ID = " & intMod)
    
    If m_strMasterVersion <> "" Then
        CheckMasterVersion = True
    Else
        CheckMasterVersion = False
    End If
    
    Exit Function
    
ErrHandler:
    MsgBox Err.Number & " " & Err.Description, vbOKOnly
    MsgBox "Access is unable to connect with the back end database.  Please contact Mike Demaris!", vbCritical + vbOKOnly, "Check Master Version"
    DoCmd.Quit

End Function

Rem     This function compares the current project path with the master file path
Private Function ComparePaths(strMasterPath As String, strCurrPath As String) As Boolean
    If Right$(strMasterPath, 13) = Right$(strCurrPath, 13) Then
        ComparePaths = True
    Else
        ComparePaths = False
    End If

End Function

Private Sub UpdateFrontEnd(ByVal LocalFilePath As String, ByVal MasterFileFolder As String, ByVal masterF As String)

    Dim l_Restart As String
    Dim l_WorkingFilePath As String
    Dim l_MasterFilePath As String

    Rem     Set the file name and location for the file to copy
    l_MasterFilePath = MasterFileFolder & "\" & masterF
    
    Rem     Set the file name of the batch file to create
    m_BatchFile = CurrentProject.path & "\UpdateDbFE.cmd"
    
    Rem     The Master full path, at Target Dir
    l_WorkingFilePath = m_targetPath & "\" & masterF
    
    Rem     Set the restart file name
    l_Restart = """" & l_WorkingFilePath & """"
    
    Rem     Create the Batch File
'   ====================================================================
'                               Production
'   ====================================================================
    Open m_BatchFile For Output As #1
    Print #1, "@Echo Off"
    Print #1, "ECHO Deleting old file..."
    Print #1, ""
    Print #1, "ping 127.0.0.1 -n 5 -w 1000 > nul"
    Print #1, ""
    Print #1, "Del """ & LocalFilePath & """"
    Print #1, ""
    Print #1, "ECHO Copying new file..."
    Print #1, "Copy /Y """ & l_MasterFilePath & """ """ & l_WorkingFilePath & """"
    Print #1, ""
    Print #1, "ECHO Starting Microsoft Access..."
    Print #1, "START /I " & """MSAccess.exe"" " & l_Restart
    Close #1

'   ====================================================================
'                               Dev
'   ====================================================================
'    Open m_BatchFile For Output As #1
'    Print #1, "@Echo Off"
'    Print #1, "ECHO Deleting old file..."
'    Print #1, ""
'    Print #1, "ping 127.0.0.1 -n 5 -w 1000 > nul"
'    Print #1, ""
'    Print #1, "ECHO Copying new file..."
'    Print #1, "Copy /Y """ & l_MasterFilePath & """ """ & l_WorkingFilePath & """"
'    Print #1, ""
'    Close #1
    
Rem    Run the batch file
    Shell m_BatchFile
    
Rem    Close the current application so batch file can execute.
    DoCmd.Quit
    
End Sub

Private Sub CopyMasterFE(ByVal LocalFilePath As String, ByVal MasterFileFolder As String, ByVal masterF As String)
Rem     LocalFilePath is the file to be deleted.
Rem     MasterFileFolder is the locatoin of the master FE to be copied.
Rem     masterF is the name of the master FE file.

    Dim l_BatchFile As String                 'full file name of the cmd file create here and ran.
    Dim l_MasterFilePath As String            'full file name of the master FE to be copied.
    Dim l_Restart As String                   'full file name of the FE to be run.
    
    Dim l_shortCutVbs As String               'full file name of the vbs file to be copied.
    Dim l_shortCutTarget As String            'TargetPath to copy the script files.
    Dim l_shortCutBat As String               'full file name of the bat file to be copied.

    l_MasterFilePath = MasterFileFolder & "\" & masterF
    l_shortCutVbs = MasterFileFolder & "\CreateShortcut.vbs"
    
    l_BatchFile = m_targetPath & "\CopyDbFE.cmd"
    l_Restart = """" & m_targetPath & "\" & masterF & """"
    
    l_shortCutTarget = m_targetPath & "\"
    l_shortCutBat = MasterFileFolder & "\CreateShortcutBAT.bat"
    
'    Debug.Print "Master file: ", l_MasterFilePath
    Debug.Print "Master File: ", l_MasterFilePath
    Debug.Print "Shortcut vbs: ", l_shortCutVbs
    Debug.Print "Restart file: ", l_Restart
    Debug.Print "Shortcut target: ", l_shortCutTarget
    Debug.Print "Shortcut bat: ", l_shortCutBat
    Debug.Print "Delete file: ", LocalFilePath
'    Stop
    
'   =======================================================
'                       Production
'   =======================================================

    Open l_BatchFile For Output As #1
    Print #1, "@Echo Off"
    Print #1, "ECHO Deleting old file..."
    Print #1, ""
    Print #1, "Del """ & LocalFilePath & """"
    Print #1, ""
    Print #1, "timeout /t 2"
    Print #1, ""
    Print #1, "ECHO Copying new file..."
    Print #1, "Copy /Y """ & l_MasterFilePath & """ """ & l_Restart & """"
    Print #1, "timeout /t 2"
    Print #1, "Copy /Y """ & l_shortCutVbs & """ """ & l_shortCutTarget & """"
    Print #1, "timeout /t 2"
    Print #1, "Copy /Y """ & l_shortCutBat & """ """ & l_shortCutTarget & """"
    Print #1, ""
    Print #1, "timeout /t 2"
    Print #1, "ECHO Starting Microsoft Access..."
    Print #1, "START /I " & """MSAccess.exe"" " & l_Restart
    Close #1

'   =======================================================
'                           Dev
'   =======================================================

'    Open l_BatchFile For Output As #1
'    Print #1, "@Echo Off"
'    Print #1, "ECHO Deleting old file..."
'    Print #1, ""
'    Print #1, "timeout /t 2"
'    Print #1, ""
'    Print #1, "Del """ & LocalFilePath & """"
'    Print #1, ""
'    Print #1, "ECHO Copying new file..."
'    Print #1, "Copy /Y """ & l_MasterFilePath & """ """ & l_Restart & """"
'    Print #1, "timeout /t 2"
'    Print #1, "ECHO Starting Microsoft Access..."
'    Print #1, "START /I " & """MSAccess.exe"" " & l_Restart
'    Close #1
    
    Shell l_BatchFile
    
    DoCmd.Quit
    
End Sub

Public Function RunBatFile(s_batFile As String, s_FE As String, s_Mod As String)
    Dim strBatch As String
    Dim strShtCutFile As String
    strShtCutFile = "CreateShortcutBAT.bat"
'    Dim strFE As String
'    Dim strMod As String
'    strMod = "BBX"
'    strFE = "BBX_fe.accdb"
    strBatch = s_batFile & "\" & strShtCutFile & " " & s_FE & " " & s_Mod
    Shell strBatch, vbNormalFocus
'Debug.Print strBatch
    
End Function
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:27
Joined
Oct 29, 2018
Messages
16,572
Something I recently noticed whilst creating a new DB is that the copied front end file from the server is not deleted, as it should be. For instance, I copy it to the desktop and run it, it should copy itself to the “\cmms” folder and delete the desktop file. I’ll be looking into that…
Perhaps, it's a timing issue?
 

mjdemaris

Working on it...
Local time
Yesterday, 23:27
Joined
Jul 9, 2015
Messages
380
That could very well be.
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 02:27
Joined
Apr 27, 2015
Messages
4,154
I'm just now seeing this. mjdemaris: have you been able to make this work? If not, Scott Prince is a member here who might be able to assist you.
 

Auntiejack56

Registered User.
Local time
Today, 17:27
Joined
Aug 7, 2017
Messages
90
Hiya,

" Then they attempt to use the front end on the server side, which is not allowed, though somehow a few have found a way to run it anyway (working on that)."

Users, eh, who needs 'em. If you have something like the following in your Autoexec or splash screen, it keeps users from opening the master version on the server.
If Command <> "GoYouGoodThing" then Application.Quit
Provided, of course, that you can start the local app with a command using the format (from memory, sorry)
C:\ProgramFiles\...\Access.exe C:\MyFELocation\MyFE.accdb /cmd GoYouGoodThing

It's not foolproof but I've had success with it.

Jack
 

Users who are viewing this thread

Top Bottom