If you have more than a couple of users accessing your database at the same time it is a wise move to split the database so that your datafiles are in a backend file on the network and each user has a locally stored front end file which links to the backend's tables. Not only will it make it easier to update the front end, but it also cuts down the amount of network traffic and makes things much less likely to corrupt. Here is how I make sure my users are using the latest version of the form every time they open the database. Put the master file in a location where anyone can get to. They don't have to be able to see it, just copy files from it.
In the front end file there is a table called version. In the autoexec macro set it to run this function:
Function CheckVersion() As Boolean Dim Thisdb As DAO.Database, ThisRs As DAO.Recordset Dim db As DAO.Database, Rs As DAO.Recordset Set db = OpenDatabase("\\WYP_MANS_1\Impact$\ImpactFrontend\ImpactXP.mdb") 'Path to master front end file in shared folder Set Thisdb = CurrentDb Set ThisRs = Thisdb.OpenRecordset("tblVersionNumber") Set Rs = db.OpenRecordset("tblVersionNumber") CheckVersion = False If Rs("Version") <> ThisRs("version") Then fHandleFile GetDBLocation & "UpdateFrontEnd.vbs", WIN_NORMAL CheckVersion = True End If Rs.Close ThisRs.Close Set Rs = Nothing Set ThisRs = Nothing If CheckVersion = True Then Application.Quit acQuitSaveNone End Function
This routine opens up the master copy amd compares the version number in that one with the version in this one. If they do not match it runs a vbscript using the code from FAQ705-1971. The vbscript file is here: 'Start Dim strOldPath Dim strNewPath Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
do while FSO.FileExists(Left(strOldPath, len(strOldPath)-3) & "ldb")=True 'As long as an ldb file exists, someone is in the database, so just do nothing until it goes. loop Set WshShell = WScript.CreateObject("WScript.Shell")
Copy it all into a text file and call it UpdateFrontEnd.vbs and save it in the same directory as the front end file. The function then closes down the access file. The script just waits in the background until you have left the access file. It then copies the new master copy over the new front end copy and opens the new file. This method only works if you or your network team have not disabled windows scripting. If they have the method can be adapted to open another access file which does the same job as the vbs file. One day I might even write a FAQ on how to do that! The only other thing you will need to get this code running is the GetDBLocation function. This just returns the path the to the current database file:
Function GetDBLocation() As String Dim db As Database Set db = CurrentDb GetDBLocation = left(db.Name, Len(db.Name) - Len(Dir(db.Name))) Set db = Nothing End Function