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
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.