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.)
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