Deploying Access with VBScript (1 Viewer)

wjburke2

Registered User.
Local time
Today, 05:09
Joined
Jul 28, 2008
Messages
194
I found this great VBScript by Bill Mosca for deploying and updateing Access DB's of all kinds. It runs great untill it gets to the line that runs the start shortcut. I am hoping someone can spot the problem.

'Launch FE.
WSHShell.Run cLOCPATH & "\" & cSCName & ".lnk"

Then errors with "System can not find the file specified.


'*******************************************************************************
' Project : Fulfillment Database (Access 2003)
'
' Title : ProjMgmt_FEUpdate.vbs
' DateTime : 10/11/2007 11:37:23
' Author : Bill Mosca
' Purpose : Installs all files needed for database application based on text
' file named ProjectMgmt_feVyyyymmdd.txt
' Index Updated on 5/21/2007)
' MakeFEShortcut - Creates/overwrites shortcut to launch
' GetDB - Copy latest files from server to local folder
' KeyExists - Checks that msAccess key exists in registry
'*******************************************************************************
Option Explicit
Const cTXTFILE = "FulfillmentDB_feVer7_2_1_3.txt"
Const cFE = "Fulfillment72.mdb"
Const cICON = "Globe.ico"

' Server Path
Const cSVRPATH = "\\appserver\Fulfillment_Database\Install"

' Local path for MDE
Const cLOCPATH = "C:\Documents and Settings\All Users\Application Data\Fulfillment_Database"

' Name of this script
Const cScriptName = "FulfillmentDB_FEUpdate_2.vbs"

' Name of animated gif used in SplashBox
Const cAniGif = "AG00174_.GIF"
Const cSCName = "StartFulfillmentDB"

'Name used for Desktop Shortcut and title of SplashBox
Const cAppName = "Fulfillment Database"

Dim WSHShell
Dim fs
Dim oIE
'*******************************************************************************
Function SplashBox(sTitle)
'*******************************************************************************
'Purpose : Creates Splash screen while front end downloads.
'DateTime : 1/11/2007 11:18
'Author : Tom Lavedas
Dim s, sBody, item
Set oIE = CreateObject("InternetExplorer.Application")
With oIE
'Commented out for IE7
'.FullScreen = True
.Toolbar = False
.RegisterAsDropTarget = False
.StatusBar = False
.Menubar = False
.Addressbar = False
.Navigate ("about:blank")
Do Until .ReadyState = 4: WScript.Sleep 100: Loop
.Width = 300: .Height = 300
With .Document
With .ParentWindow.Screen
oIE.Left = (.availWidth - oIE.Width) \ 2
oIE.Top = (.availheight - oIE.Height) \ 2
End With

sBody = "Please wait while latest version of " & cAppName & " loads..."

s = "<html><head><title>" & sTitle _
& "</title></head><script language=vbs>bWait=true<" & "/script>" _
& "<body bgColor=#0d8499><center>" _
& "<center> </center>" _
& "<center> </center>" _
& "<center>" _
& "<font face=" & Chr(34) & "Comic Sans MS" & Chr(34) & " color=#d4d4d4>" _
& sBody & "</font><p>" _
& "<img alt=" & Chr(34) & Chr(34) _
& "src=" & Chr(34) & "file:///" & cSVRPATH & "\" & cAniGif & Chr(34) _
& "align=bottom>"
s = s & "<br><br> <input id=Button1 type=button value=Cancel onclick =Window.Close()>"
s = s & "</center></body></html>"
.Open
.Write (s)
.Close
Do Until .ReadyState = "complete": WScript.Sleep 50: Loop
With .body
.Scroll = "no"
.Style.BorderStyle = "outset"
.Style.BorderWidth = "3px"
End With
oIE.Visible = True
CreateObject("Wscript.Shell").AppActivate sTitle
On Error Resume Next
On Error GoTo 0
End With
End With

End Function

'*******************************************************************************
Public Function MakeFEShortcut()
'*******************************************************************************
'Purpose : Creates/overwrites shortcut to launch
' front end.
Dim Shortcut, DesktopPath, StartupPath
Dim MSAccPath

On Error Resume Next

'Kill shortcut so icon will be default Access icon.
If fs.FileExists(cLOCPATH & "\" & cSCName & ".lnk") Then
fs.DeleteFile cLOCPATH & "\" & cSCName & ".lnk", True
End If
'Create shortcut in same folder as FE.
Set Shortcut = WSHShell.CreateShortcut(cLOCPATH & "\" & cSCName & ".lnk")

'Get msaccess path
MSAccPath = WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\" _
& "CurrentVersion\App Paths\MSACCESS.EXE\Path")

If Err <> 0 Then
MsgBox "MS Access is not installed. Installation aborted."
Set Shortcut = Nothing
Err = 0
Exit Function
End If

Shortcut.TargetPath = """" & MSAccPath & "\msaccess.exe" & """"
Shortcut.arguments = """" & cLOCPATH & "\" & cFE & """"

StartupPath = MSAccPath
If fs.FolderExists(StartupPath) Then
Shortcut.WorkingDirectory = StartupPath
End If
Shortcut.Description = "Application"
'Commented out. Use default Access Icon for this shortcut.
'Shortcut.IconLocation = cLOCPATH & "\" & cICON
Shortcut.Save

Set Shortcut = Nothing
MakeFEShortcut = True
End Function

'*******************************************************************************
Sub GetDB()
'*******************************************************************************
'Purpose : Copy latest files from server to local folder

If Not fs.FolderExists(cLOCPATH) Then
fs.CreateFolder (cLOCPATH)
End If

fs.CopyFile cSVRPATH & "\" & cTXTFILE, cLOCPATH & "\", True
If Err.Number <> 0 Then
MsgBox Err.number & "-" & err.Description
End If
fs.CopyFile cSVRPATH & "\" & cFE, cLOCPATH & "\", True
If Err.Number <> 0 Then
MsgBox Err.number & "-" & err.Description
End If
fs.CopyFile cSVRPATH & "\" & cICON, cLOCPATH & "\", True
If Err.Number <> 0 Then
MsgBox Err.number & "-" & err.Description
End If

End Sub

'*******************************************************************************
Public Function MakeDesktopShortcut(sName, target)
'*******************************************************************************
'Purpose : Create new desktop shortcut in case something has changed.
Dim Shortcut, DesktopPath, StartupPath

DesktopPath = WSHShell.SpecialFolders("Desktop")
Set Shortcut = WSHShell.CreateShortcut(DesktopPath & "\" & sName & ".lnk")
Shortcut.TargetPath = target
StartupPath = fs.GetParentFolderName(target)

If fs.FolderExists(StartupPath) Then
Shortcut.WorkingDirectory = StartupPath
End If

Shortcut.IconLocation = cLOCPATH & "\" & cICON
Shortcut.Save

End Function
'*******************************************************************************
Sub main()
'*******************************************************************************
'Splash screen to let user know something is going on.
Call SplashBox(cAppName)
Set WSHShell = WScript.CreateObject("WScript.Shell")
Set fs = WScript.CreateObject("Scripting.FileSystemObject")
'See if latest text file is in local folder. Also check if FE is missing.
If fs.FileExists(cLOCPATH & "\" & cTXTFILE) = False _
Or fs.FileExists(cLOCPATH & "\" & cFE) = False Then
On Error Resume Next
fs.DeleteFile(cLOCPATH & "\" & "FulfillmentDB_feVer*.txt")
Err.clear
Call GetDB
End If

If Err.Number <> 0 Then
MsgBox Err.number & "-" & err.Description
Else
'Just in case we need a new FE shortcut...
If MakeFEShortcut = False Then
Set fs = Nothing
Set WSHShell = Nothing
WSCRIPT.QUIT(0)
Exit Sub
End If

MakeDesktopShortcut cAppName, cSVRPATH & "\" & cScriptName

'Occasionally MSAccess opens before filecopy is finished.
WScript.Sleep 5000

'Launch FE.
WSHShell.Run cLOCPATH & "\" & cSCName & ".lnk"

End If
oIE.Quit
set oIE = Nothing
Set fs = Nothing
Set WSHShell = Nothing
WSCRIPT.QUIT(0)

End Sub

'*******************************************************************************
'VBScript Starting point
Call main
'*******************************************************************************
 

wrmosca

MS Access MVP
Local time
Today, 03:09
Joined
Dec 5, 2006
Messages
13
I glad you like my code! I just happened to stop by here because PBaldy told me you posted a question about my script.

I think the probelm lies in the file path containing spaces. Try surrounding the entire path with double quotes.

WSHShell.Run """" & cLOCPATH & "\" & cSCName & ".lnk" & """"
 

wjburke2

Registered User.
Local time
Today, 05:09
Joined
Jul 28, 2008
Messages
194
Bill, it is great that you took the time to answer this. Double quotes worked like a charm by the way. I think I even read about that on you site. I have tried every deployment strategy I could find. Between the network restrictions, the users limited access to hard drives, and offices fidgetiness it has been a headache. Thank you for you wonderful solution.
This and more can be found at http://thatlldoit.com/howtosarticles.aspx#fe_updater
 

wrmosca

MS Access MVP
Local time
Today, 03:09
Joined
Dec 5, 2006
Messages
13
You're very welcome WJ (Your name?). And thanks for the plug.;) If you have any questions about the tools I made feel free to contact me directly though my Contact Us page.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 06:09
Joined
Apr 27, 2015
Messages
6,286
Bill,

I came across your code about 2 years ago, unfortunately it was part of an application I inherited and the unscrupulous individual replaced your name with his.

I have scoured the globe for a FE updater and I have to say that this is the most comprehensive way to deploy and update an Access FE. Can’t tell you enough how impressed and grateful I am.

I forgot how i stumbled across your website and found this code, but I have since gone back reinstated the proper credit to the rightful person.

Thanks again!
 

wrmosca

MS Access MVP
Local time
Today, 03:09
Joined
Dec 5, 2006
Messages
13
Thank you for giving me credit for my code. I'll never understand why someone would want to take credit for the work of others, but there are lots of people who do just that. In my mind they are losers who will never really succeed in life. They will have to lie about their own worth when they could have demonstrated how they were successful in finding the right solution...an act that is in many ways just as important as creating the solution itself.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 06:09
Joined
Apr 27, 2015
Messages
6,286
Just in case someone happens to want to use this code in runtime mode, you need to alter the code:

Code:
'From this
Shortcut.TargetPath = """" & MSAccPath & "\msaccess.exe" & """"
Shortcut.arguments = """" & cLOCPATH & "\" & cFE & """"

' To this
Shortcut.TargetPath = """" & MSAccPath & "\msaccess.exe" & """"
Shortcut.arguments = """" & cLOCPATH & "\" & cFE & """/runtime"

Good stuff Bill! My co-workers think I am a genius and I am in NO hurry to tell them differently, what with the contract up for rebid!
 

JaberSa

New member
Local time
Today, 13:09
Joined
Jan 20, 2024
Messages
9
Anyone knows where I could downnload the text file??? The one mentioned in the code, to see all the necessary files the script is referring to.
It should be this reference: Const cTXTFILE = "FulfillmentDB_feVer7_2_1_3.txt"
Or perharps this: ProjectMgmt_feVyyyymmdd.txt
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 06:09
Joined
Apr 27, 2015
Messages
6,286
Anyone knows where I could downnload the text file??? The one mentioned in the code, to see all the necessary files the script is referring to.
It should be this reference: Const cTXTFILE = "FulfillmentDB_feVer7_2_1_3.txt"
Or perharps this: ProjectMgmt_feVyyyymmdd.txt
That is a text file you have to make yourself. I used it record the changes I would make between the versions.
 

JaberSa

New member
Local time
Today, 13:09
Joined
Jan 20, 2024
Messages
9
That is a text file you have to make yourself. I used it record the changes I would make between the versions.
Ok thanks a lot NauticalGent. Anyway I realized the code above created by wrmosca loads a new copy and over-writes the old one each time you start access from the shortcut created on the desk top. So this keeps the database updated all the time. This is such a wonderful job by wrmosca. Really saved me a lot. Thanks a lot wrmosca for putting up all the effort and coming up with such a wonderful code.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 06:09
Joined
Apr 27, 2015
Messages
6,286
Rumor has it that VB Script is going away - that isn't to say this will stop working immediately, just that VB Script is no longer being supported. With that in mind, I have, with a huge amount of help from ChatGPT, converted this to PowerShell. If you are interested, I can share that.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:09
Joined
Feb 19, 2002
Messages
42,981
I use a much simpler distribution method.
Code:
md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb
This batch file is located on the server in the directory with the master copy of the FE for ease of changing. Each user has a desktop shortcut that runs the batch file. As you can see, the batch file downloads a new copy of the FE each time it runs. The FE and BE both need version tables and the BE version table is always updated when a new FE is distributed. The server version table has a FE version and a BE version. At login, the app verifies that the two FE versions match.
 

JaberSa

New member
Local time
Today, 13:09
Joined
Jan 20, 2024
Messages
9
Rumor has it that VB Script is going away - that isn't to say this will stop working immediately, just that VB Script is no longer being supported. With that in mind, I have, with a huge amount of help from ChatGPT, converted this to PowerShell. If you are interested, I can share that.
Yes! Please! I would love to see that.
 

JaberSa

New member
Local time
Today, 13:09
Joined
Jan 20, 2024
Messages
9
I use a much simpler distribution method.
Code:
md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb
This batch file is located on the server in the directory with the master copy of the FE for ease of changing. Each user has a desktop shortcut that runs the batch file. As you can see, the batch file downloads a new copy of the FE each time it runs. The FE and BE both need version tables and the BE version table is always updated when a new FE is distributed. The server version table has a FE version and a BE version. At login, the app verifies that the two FE versions match.
Wow! This one looks quite simple and straight forward. Am gonna give it a try . Thanks a lot Pat Hartman.
 

JaberSa

New member
Local time
Today, 13:09
Joined
Jan 20, 2024
Messages
9
I was asked to introduce myself. So here I am.
Am a Kenyan currently based in Yemen. I am working for PetroMasila petroleum company in Yemen as oil well field operator. I've been creating many applications for the company in Excel VBA and currently switched to Access. I do love VBA but also have experience in VB6 and VB.Net.
I might say am at intermediate level in Access and looking forward to excel to expert level. I believe am at the right spot after digging so much information in Access World. Thanks a lot guys.
 

I am alive

Member
Local time
Today, 13:09
Joined
Aug 29, 2020
Messages
139
I glad you like my code! I just happened to stop by here because PBaldy told me you posted a question about my script.

I think the probelm lies in the file path containing spaces. Try surrounding the entire path with double quotes.

WSHShell.Run """" & cLOCPATH & "\" & cSCName & ".lnk" & """"
Thanks so much sir. Any sample database for this code sir? Thanks in advance sir.
 

Users who are viewing this thread

Top Bottom