Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-17-2019, 11:38 AM   #1
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 362
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
My Current Version of Front End Updater

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

__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Old 07-17-2019, 11:40 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,562
Thanks: 38
Thanked 869 Times in 852 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: My Current Version of Front End Updater

Quote:
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-17-2019, 11:45 AM   #3
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 362
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: My Current Version of Front End Updater

That could very well be.

__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Front-end updater on SharePoint Online? CNorway General 3 05-24-2018 11:57 AM
Front End Auto Updater and Group Policy dkmoreland Modules & VBA 14 02-08-2018 09:29 AM
Access full version or Access Runtime version for deploying the front-end? docxyz General 3 11-24-2014 11:51 PM
ONe back end and two different version front ends issues when compacting/syn PuddinPie General 2 04-06-2011 10:12 AM
Auto Front End Updater Patrick Brydone General 11 02-11-2006 10:47 AM




All times are GMT -8. The time now is 12:26 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World