The following VB Script demonstrates how it can be used to copy a new front
end from a server to a local machine each time the user clicks on it and then
automatically start Access. From the user's perspective, they don't know any
difference. You can use the same technique in situations where the front end
might move around or you need to point the users to a different file.
Basically, use the script to create a shortcut on their desktop that points
to the script and then within the script make the neccessary changes. That
way you won't have to worry about deleting a shortcut if you move the front
end - just keep the script in the same location.
'--------------------------------------------------------------------------------------------
' Author : David C. Holley
' Date : 9/1/2008
' Purpose : Temporary startup script which backsup the local .MDE file (if
present) and then
' copies the current .MDE file from the server to the local
machine. Currently,
' there are two temp tables in the front end which can lead to
database bloat.
' Copying the front end each time its started is a temporary
stop-gap until I
' can spin out the temp tables to their own .MDB.
'--------------------------------------------------------------------------------------------
'10
Dim wshShell
Dim fso
Dim strMsgText
Dim strLocalTargetFolder
Dim strServerSourceFolder
Dim appIE
Dim ieWindow
'18
Set fso = createObject("Scripting.FileSystemObject")
Set wshShell = createObject("Wscript.shell")
'--------------------------------------------------------------------------------------------
Set appIE = CreateObject("InternetExplorer.Application")
appIE.Offline = True
appIE.AddressBar = False
appIE.Height = 200
appIE.Width = 350
appIE.MenuBar = False
appIE.StatusBar = False
appIE.Silent = True
appIE.ToolBar = False
appIE.Navigate ""
Do While appIE.Busy
WScript.Sleep 100
Loop
appIE.document.Open
Set ieWindow = appIE.document
ieWindow.Write "<html><head><title>"
ieWindow.Write "Microsoft Access"
ieWindow.Write "</title></head><body id='bodyTag' scroll=no
style=font-size:8pt;font-family:Tahoma;background-color:#D4D0C8>"
ieWindow.Write "<div style=font-weight:800;>"
strMsgText = ""
strMsgText = strMsgText & "The application will start momentarily.
Please wait while your PC is checked for the most current version." & Chr(13)
strMsgText = strMsgText & "There may be a short delay if files need to be
copied to your machine. Please wait.<br>"
ieWindow.write strMsgText
ieWindow.write "</div>"
ieWindow.Write "</body></html>"
appIE.Visible = True
'--------------------------------------------------------------------------------------------
'50
strLocalTargetFolder = "C:\\Documents and Settings\\All
Users\\Documents\\Trailer Management and Manifest\\Application"
strServerSourceFolder =
"P:\\Orlando\\Branch\\Data\\Production\\Operations\\Trailer Management and
Manifest\\Application"
ieWindow.GetElementById("bodyTag").innerHTML =
ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Checking local
folders..."
'Create the folders if they don't exist
call createLocaLFoLders
'58
ieWindow.GetElementById("bodyTag").innerHTML =
ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Local folders exist..."
ieWindow.GetElementById("bodyTag").innerHTML =
ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Copying Front End .MDE
file to local machine..."
'Copy the front end to the hard drive, overright if it already exists
fso.copyfile strServerSourceFolder & "\\Trailer Management and Manifest
Front End.mde", strLocalTargetFolder & "\\Trailer Management and Manifest
Front End.mde", true
ieWindow.GetElementById("bodyTag").innerHTML =
ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Checking desktop
shortcut..."
createShortcut
'Copy the short cut to this script if it doesn't exist
fso.copyfile strServerSourceFolder & "\\Start Trailer Management and
Manifest Database.lnk", "C:\\Documents and Settings\\All
Users\\Desktop\\Start Trailer Management and Manifest Database.lnk", true
'71
ieWindow.GetElementById("bodyTag").innerHTML =
ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Starting Access..."
wshShell.run "Msaccess.exe " & chr(34) & strLocalTargetFolder & "\\Trailer
Management and Manifest Front End.mde" & Chr(34)
appIE.quit
Set IEwindow = Nothing
Set appIE = nothing
Set wshShell = Nothing
Set fso = nothing
'82
sub createLocaLFoLders
Dim targetFolder
Dim folderExists
'87
targetFolder = "C:\\Documents and Settings\\All Users\\Documents\\Trailer
Management and Manifest"
folderExists = FSO.FolderExists(targetFolder)
'90
if folderExists = False then
fso.CreateFolder targetFolder
end if
targetFolder = "C:\\Documents and Settings\\All
Users\\Documents\\Trailer Management and Manifest\\Application"
'95
folderExists = FSO.FolderExists(targetFolder)
if folderExists = False then
fso.CreateFolder targetFolder
end if
end sub
'103
sub createShortcut
aSplit = split(wscript.scriptname, ".")
if Not fso.fileExists (aSplit(0) & ".lnk") then
set newShortcut = wshShell.createShortcut (strServerSourceFolder & "\\" &
aSplit(0) & ".lnk")
newShortcut.TargetPath = WScript.ScriptFullName
newShortCut.WorkingDirectory = strServerSourceFolder
newShortcut.IconLocation = "C:\Program Files\Microsoft
Office\OFFICE11\\msaccess.exe, 0"
newShortCut.Description = "Start Trailer Management and Manifest
Database"
newShortCut.Save
end if
end sub
