Multiple simultaneous user db really should be split.
As for strategies to manage frontend updating, here is code showing how I used to accomplish. Unfortunately, computer updates no longer allow programmatic copying of files and I had to modify this code to only notify users with a MsgBox popup to copy new version to their local drive folder and code opens source folder. Due to lack of IT support, this was the best I could do.
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
Frontend links to tables in backend. All data entry/edit and imports done via links. If you want to replace all records in table, then delete them and import new set. How do you accomplish this now without disrupting work?
I did build 1 multi-user db that worked without issues which really surprised me but there was zero code (no macros nor VBA). It rarely had more than 1 person at a time using but testing with 2 users at one time worked.