Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Modules (VBA Coding) FAQ

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: 7 Jun 02 (Edited 3 Jul 02)

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
    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:
Dim strOldPath
Dim strNewPath

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.
Set WshShell = WScript.CreateObject("WScript.Shell")

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


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



Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close