Link to home
Start Free TrialLog in
Avatar of Ernest Grogg
Ernest Grogg

asked on

Update Access FrontEnd by Version #

Hello,

I used the below code from this Question:

How to update a local Access front end via a shell command within Access

saved it on my desktop as:  Deployment.vbs

But I get an error code at line  41 Char 1

"Type Mismatch: 'UBound'

Any help would be great!


'declare an object to be a filesystem object
Public fs
'declare an object to be a MS Access application
Public acApp
 'declare a wscript shell
Public WshShell

Main()

'-------------------------------------------------------------------------------------------------------------------------------------------
Sub Main()
dim MyConn
dim MyConn1
Dim LocalSplit
Dim ServerSplit
Dim x

OpenCmdWindow()


Set fs = CreateObject("Scripting.FileSystemObject")
CheckDeployment()

If fs.FileExists("C:\Databases\DepolomentVersion.txt") then
    Set MyConn= fs.OpenTextFile("C:\Databases\DeploymentVersion.txt",1, true)
    ServerSplit = Split(MyConn.readall, ".")
    MyConn.Close
end if

If fs.FileExists("C:\Databases\PathToLocalVersion.txt") then
    Set MyConn1= fs.OpenTextFile("C:\Databases\LocalVersion.txt",1, true)
    LocalSplit = Split(MyConn1.readall, ".")
    MyConn1.Close
else
    LocalSplit =Split("0.0.0.0", ".")
end if

set myconn = nothing
set myconn1 = nothing

If UBound(LocalSplit) <> UBound(ServerSplit) Then
        Call CopyUpdate()
        FireDB()
        Exit Sub
End If

For x = 0 To UBound(LocalSplit)
    If cint(ServerSplit(x)) > cint(LocalSplit(x)) Then
        Call CopyUpdate()
        FireDB()
        Exit Sub
    End If
Next

FireDB()

end sub

'------------------------------------------------------------------------------------
private Sub CopyUpdate()
Dim BuiltPath
Dim userpath

userpath= "C:\Deployment\AMLD3055DB.ACCDB"
BuiltPath = "C:\LocalVersion\AMLD3055DB.ACCDB"
TempPath = "c:\prod\temp"

If fs.FolderExists(BuiltPath) = False Then
    fs.CreateFolder BuiltPath
End If

fs.copyfile userpath, BuiltPath, -1
fs.copyfile "C:\Databases\DeploymentVersion.txt", "C:\Databases\LocalVersion.txt", -1



End Sub
'-----------------------------------------------------------------------------------------

Function CheckDeployment() 
Dim BuiltPath
BuiltPath = "C:\LocalVersion\AMLD3055DB.ACCDB"

If fs.FileExists(BuiltPath) = False Then
    Call CopyUpdate()
End If



End Function
'---------------------------------------------------------------------------------------
sub FireDB

CloseCmdWindow()
dim PathToMDE
PathToMDE = "C:\LocalVersion\AMLD3055DB.ACCDB"



Set acApp=CreateObject("Access.Application")
acApp.usercontrol=true
acApp.DoCmd.RunCommand (10) 'accmdappmaximize
acApp.DoCmd.Hourglass True


acApp.AutomationSecurity=1  ' low
acApp.OpenCurrentDatabase PathToMDE
acApp.DoCmd.Hourglass false
Set acApp=Nothing
Set fs = nothing

end sub

'-------------------------------------------------------------
sub OpenCmdWindow()
set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "cmd.exe"
WScript.Sleep 10
WshShell.AppActivate "c:\windows\system32\cmd.exe"
WScript.Sleep 100
WshShell.SendKeys "Starting....wait patiently...."


end sub

 '------------------------------------------------------------
sub CloseCmdWindow()
dim oshell
dim oWmg
dim strWndprs
dim objQResult

WScript.Sleep 100

Set oShell = CreateObject("WScript.Shell") 
Set oWmg = GetObject("winmgmts:") 

strWndprs = "select * from Win32_Process where name='cmd.exe'" 
Set objQResult = oWmg.Execquery(strWndprs) 

For Each objProcess In objQResult 
	intReturn = objProcess.Terminate(1) 
Next
End sub

Open in new window

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

check the name of the file in line 24

24:If fs.FileExists("C:\Databases\DepolomentVersion.txt") then

is this correct?
and what is supposed to happen if the IF statement on 24 fails; your ServerSplit() will never be instantiated. and the UBOUND(ServerSplit) will probably return an error.

You probably need an Else statement in that IF / End If construct to make sure that ServerSplit gets some value.

Dale
Avatar of Ernest Grogg
Ernest Grogg

ASKER

OK...got that fixed,

so now...can't get past line 73

checked and path is OK?
try deleting the file first

      fs.deletefile  "C:\Databases\LocalVersion.txt"
73:fs.copyfile "C:\Databases\DeploymentVersion.txt", "C:\Databases\LocalVersion.txt", -1
OK, nevermind...I found that issue with the path.

But

I get stuck here:  (says Active X can't create object: Access.Application)...

Set acApp=CreateObject("Access.Application")
that is not a vbscripting command

what office version are you using?
2013
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good..

on my system it is:

C:\Program Files\Microsoft Office 15\root\office15\MSAccess.exe

but perfect...