Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I make sure all users are using the most up to date version of the front end?

Multi User Databases

How do I make sure all users are using the most up to date version of the front end?

by  oharab  Posted    (Edited  )
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

strOldPath=left(wscript.scriptfullname,len(wscript.scriptfullname)-len(wscript.scriptname)) & "ImpactXP.mdb"
strNewPath ="\\WYP_MANS_1\Impact$\ImpactFrontend\ImpactXP.mdb"

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

FSO.CopyFile strNewpath,strOldPath,True
wshshell.run stroldpath

'End

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

Enjoy.

Ben
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top