Following is a procedure I used to use to assure user has latest version of FE. Unfortunately, file copy method quit working after new computers set up and IT permissions no longer allowed programmatic copy of files. Had to modify to just open network folder location of new file for user to manually copy to local C:\ location.
Code:
Private Sub Form_Load()
'Check for updates to the program on start up - if values don't match then there is a later version
If Me.tbxVersion <> Me.lblVersion.Caption Then
'because administrator opens the master development copy, only run this for non-administrator users
If DLookup("Permissions", "Users", "UserNetworkID='" & Environ("UserName") & "'") <> "admin" Then
'copy Access file
CreateObject("Scripting.FileSystemObject").CopyFile _
gstrBasePath & "Program\Install\MaterialsDatabase.accdb", "c:\", True
'allow enough time for file to completely copy before opening
Dim Start As Double
Start = Timer
While Timer < Start + 3
DoEvents
Wend
'load new version - SysCmd function gets the Access executable file path
'Shell function requires literal quote marks in the target filename string argument, apostrophe delimiters fail, hence the quadrupled quote marks
Shell SysCmd(acSysCmdAccessDir) & "MSAccess.exe " & """" & CurrentProject.FullName & """", vbNormalFocus
'close current file
DoCmd.Quit
End If
Else
'tbxVersion available only to administrator to update version number in Updates table
Me.tbxVersion.Visible = False
Call UserLogin
End If
End Sub
Private Sub tbxUser_AfterUpdate()
If Me.tbxUser Like "[A-z][A-z][A-z]" Or Me.tbxUser Like "[A-z][A-z]" Then
CurrentDb.Execute "INSERT INTO Users(UserNetworkID, UserInitials, Permissions) VALUES('" & VBA.Environ("UserName") & "', '" & UCase(Me.tbxUser) & "', 'staff')"
Call UserLogin
Else
MsgBox "Not an appropriate entry.", vbApplicationModal, "EntryError"
End If
End Sub
Private Sub UserLogin()
Me.tbxUser = DLookup("UserInitials", "Users", "UserNetworkID='" & Environ("UserName") & "'")
If Not IsNull(Me.tbxUser) Then
CurrentDb.Execute "UPDATE Users SET ComputerName='" & VBA.Environ("ComputerName") & "' WHERE UserInitials='" & Me.tbxUser & "'"
DoCmd.OpenForm "Menu", acNormal, , "UserInitials='" & Me.tbxUser & "'", , acWindowNormal
DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub