So, I had decided to go with a VBScript to determine if the current DB on a users local PC is current or not. The entire process requires the VBScript, a .txt file to house the current DB Version information, and a .hta file to display a SplashScreen so users know something is happening while the script is running.
Upon executing the VBScript, one of the first items called is the SplashScreen, which is an .hta file (which is an HTML driven file). Below is the code I used in my .hta file, but it can be modified as you need:
Code:
<html>
<HTA:APPLICATION
SysMenu="no"
SCROLL="no"
>
<head>
<script language = "VBScript">
Sub Window_OnLoad
window.resizeTo 400,250
idTimer = window.setTimeout("PausedSection", 10000, "VBScript")
End Sub
Sub PausedSection
window.close
End Sub
</script>
<title>DB Version Check ...</title>
</head>
<body style="background-color:#4f81bd; text-align:center;">
<p style="font-family:Verdana; font-size:12pt; color:white; font-weight:bold;">Please wait while we check for the latest version of the Database ...</p>
<br />
<img src="Open.gif" alt="" />
</body>
</html>
Next, you will need to create a .txt file that contains only a single line in it. The line that I use is: VER=x.x.xx (where the x's are the current version number). In the VBScript, it does a comparison of the lines inside of the .txt files on the user's local PC and the Network location where the master .txt file is stored. If they are the same, then the VBScript simply opens the existing FE DB on the users PC. If they are different, the VBScript copies the new DB to the users PC prior to opening it.
Finally, the VBScript that I setup is as follows (feel free to use and modify as you see fit):
Code:
'******************************************************************************************
' Project : Database (Access 2007)
'
' Title : ERDB.vbs
' Date : 2012/04/05
' Author : gopherking
' Purpose : Validates the current DB Front End version on the user's PC with the
' network version and then either opens the existing DB or copies the
' current DB to the user's PC and opens it. The file to confirm the DB
' version is in the format of DBVer.txt and should be located in
' the user's local Documents folder
' Release Notes:
' 2012/04/05 - Initial creation of script file
'******************************************************************************************
Option Explicit
'Version Text File Name
Const VerFile = "DBVer.txt"
'Local Front End DB name
Const cLOCFE = "FE_DB.accde"
'Network path for DB and Version file
Const cSVRPath = "\\EnterYour\Network\Path\Here"
'Network Front End DB name
Const cSVRFE = "DB_FrontEnd.accde"
'Name used for Database
Const cAppName = "My Database"
'Use any gif you would like here
Const cAniGif = "graphics\open.gif"
Const ForReading = 1
Dim WSHShell
Dim fs
Dim oIE
Dim LocVer
Dim SvrVer
Dim cUser
Dim cLOCPath
Dim WshNetwork
Sub GetDB()
If fs.FileExists(cLOCPath & "\" & VerFile) Then
fs.DeleteFile(cLOCPath & "\" & VerFile)
End If
If fs.FileExists(cLOCPath & "\" & cLOCFE) Then
fs.DeleteFile(cLOCPath & "\" & cLOCFE)
End If
fs.CopyFile cSVRPath & "\" & VerFile, cLOCPath & "\" & VerFile, True
fs.CopyFile cSVRPath & "\" & cSVRFE, cLOCPath & "\" & cLOCFE, True
'pause the script to allow for DB to download before opening ...
WScript.Sleep 5000
End Sub
Sub GetVer(vType)
Dim objRegEx, objfile, colMatches, strSearchString, strMatch
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Pattern = "VER="
If vType = "LOC" Then
Set objFile = fs.OpenTextFile(cLOCPath & "\" & VerFile, ForReading)
Else
Set objFile = fs.OpenTextFile(cSVRPath & "\" & VerFile, ForReading)
End If
Do Until objFile.AtEndOfStream
strSearchString = objFile.ReadLine
Set colMatches = objRegEx.Execute(strSearchString)
If colMatches.Count > 0 Then
For Each strMatch in colMatches
If vType = "LOC" Then
LocVer = strSearchString
'WScript.Echo "Local Version: " & LocVer
Else
SvrVer = strSearchString
'WScript.Echo "Server Verion: " & SvrVer
End If
Next
End If
Loop
objFile.Close
End Sub
Sub SetVars()
Set WshNetwork = WScript.CreateObject("WScript.Network")
'WScript.Echo "Current User Name: " & WshNetwork.UserName
cUser = WshNetwork.UserName
'WScript.Echo "cUser Variable: " & cUser
cLOCPath = "C:\Users\" & cUser & "\Documents\"
End Sub
Sub main()
'Set Global Variables that are not Constants
Call SetVars
Set WSHShell = WScript.CreateObject("WScript.Shell")
Set fs = WScript.CreateObject("Scripting.FileSystemObject")
'Open the splashscreen so they know something is going on
WSHShell.Run("ScriptFiles\Splash.hta")
If fs.FileExists(cLOCPath & "\" & VerFile) Then
Call GetVer("LOC")
If fs.FileExists(cSVRPath & "\" & VerFile) Then
Call GetVer("SVR")
If LocVer <> SvrVer Then
'MsgBox "LocVer and SvrVer Diff; Getting the latest DB"
Call GetDB
End If
Else
MsgBox "An unexpected Error has occurred. Your existing database will be " & _
"opened at this time." & chr(13) & chr(13) & "Please report this error by taking " & _
"a screen shot of this message and email it to the Administrator." _
& chr(13) & chr(13) & "Error: Missing Server Version File" & chr(13) & _
"Description: The expected file was not found in the specific network " & _
"location",0 & 16,"Missing Network Version File"
If fs.FileExists(cLOCPath & "\" & cLOCFE) = False Then
On Error Resume Next
'MsgBox "No Local FE DB; Getting the latest DB"
Call GetDB
End If
End If
Else
'MsgBox "No Local Version File; Getting the latest DB"
Call GetDB
End If
'MsgBox "opening the local DB"
'Open the DB on the PC
WSHShell.Run cLOCPath & "\" & cLOCFE
Set fs = Nothing
Set WSHShell = Nothing
WSCRIPT.QUIT(0)
End Sub
'*****************************************************************************************
'VBScript Starting Point
Call main
'*****************************************************************************************
I hope that this can help others as it definitely helped me.