Solved

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

Posted on 2014-12-01
31
292 Views
Last Modified: 2014-12-03
I setting up a front end deployement system that will execute when a user opens up his or her local copy  of a front-end so that the most recent copy on the network drive will overwrite an existing older version on the user's drive.  I'm thinking it should be on the  load event of the main form. I now have a local table on the front-ends, named LocalVersion that has the version number of the current version.  On the network drive, I have an mde file that contains only a single table with a single row, This row has the version number of the latest version of the front-end.  Each front-end is linked to this table, when the user starts up their copy of the front-end, an autoexec macro is executed that checks to see that the front-end is linked to the backend.  After this, I will compare the version number on the local version table to the version number in the RemoteVersion table via a dlookup function within an if statement.  If it is less than the remote version number, I want to overwrite the existing local user front-end via a shell batch command from within access.  Do I first have to close the existing local user front end before overwriting the local access front-end? If so, if the how will the rest of the batch file execute.  I'm not all that familiar with executing a batch file within a shell command on application start up.  I think I've got most of the pieces in place for this to work (other than making sure everyone is using the same mapping structure).  Thanks.
0
Comment
Question by:chtullu135
  • 12
  • 11
  • 5
  • +2
31 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<< Do I first have to close the existing local user front end before overwriting the local access front-end?>>

 Yes.  You can't over write a currently opened file.  You have a few choices:

1. Message the user to execute a batch script themselves.

2. Call another DB, but build in a delay.  It copies over the real DB, then opens it back up.

3. Use a "launcher" program; user always  executes this to start the app, it performs the check, copies the file if need be, then starts the app.

 There are a multitude of FE updater's out on the net.   The one from Tony Towes is quite good and there are others.   May want to simply get one of those.

Jim.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
"Do I first have to close the existing local user front end before overwriting the local access front-end?"

You cannot overwrite a file that is in use.

Personally, I would suggest opening something else (maybe a third Access database, maybe a VB program) which checks it, then overwrites it (if necessary), then opens it.

Alternatively, don't bother checking it. Have a batch file (or VB programme) which the user opens, which automatically overwrites the front end (whether it needs it or not), then opens the program. I do this as part of the log in for that user's desktop.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
BTW, here's a link to Tony's:

http://autofeupdater.com/

Jim.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
I do it with a VBScript.
You can do it in VBA with a full-blown mdb, but then you incur the overhead of opening Access.
One VBS script punches the dev version to the deployment folder and updates ver.txt -- which is the signal to the other VBS script that a new version is out.  Substitute italicized text for appropriate values

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

Main()

Sub Main()
call UpdateDBversion
Set acApp=Nothing
fs.CopyFile "ThePathToTheDevVersion", "TheDeploymentFolder"
Set fs = nothing
msgbox "Done"
end sub
'-------------------------------------------------------------------------------------------------------------------------------------------
sub UpdateDBversion

dim myconn
dim db
dim rs
Dim LocalSplit
Dim splitstring
dim PathToMDE
dim x
dim SQL
PathToMDE = "ThePathToTheDevVersion"

Set MyConn = CreateObject("ADODB.Connection")
MyConn.Open "AnAppropriateConnectionString"
Set RS = MyConn.Execute("Select versionnumber from tblVersion;")


splitstring = rs.fields("VersionNumber")
LocalSplit = split(splitstring,".")
x = UBound(LocalSplit)
LocalSplit(x) = LocalSplit(x) +1

MyConn.Execute("UPDATE tblVersion SET tblVersion.VersionNumber = " & chr(39) & join(localsplit,".") & chr(39))
MyConn.close

call UpdateVertxt(join(localsplit,"."))

end sub

'---------------------------------------------------------------------
public sub UpdateVertxt(newVer)
Set fs = CreateObject("Scripting.FileSystemObject")
dim Mytextfile

If fs.FileExists(PathToVerDotTxt) then
    fs.deleteFile(PathToVerDotTxt )
    set mytextfile = fs.OpenTextFile(PathToVerDotTxt,8, true)
    mytextfile.writeline(newver)
    mytextfile.Close
end if

end sub


The client end uses this script
'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(PathToDeployVerDotTxt) then
    Set MyConn= fs.OpenTextFile(PathToDeployVerDotTxt,1, true)
    ServerSplit = Split(MyConn.readall, ".")
    MyConn.Close
end if

If fs.FileExists(PathToLocalVerDotTxt) then
    Set MyConn1= fs.OpenTextFile(PathToLocalVerDotTxt,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= PathToDeploymentVerison
BuiltPath = PathToLocalVersion
TempPath = "c:\prod\temp"

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

fs.copyfile userpath, BuiltPath, -1
fs.copyfile PathToDeployVerDotTxt, PathToLocalVerDotTxt, -1



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

Function CheckDeployment() 
Dim BuiltPath
BuiltPath = PathToLocalVersion

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



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

CloseCmdWindow()
dim PathToMDE
PathToMDE = PathToLocalVersion

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

Open in new window


Easy for me to push our a new version, each time the client double-clicks the icon, The version gets check, and if needed a new one gets pulled
0
 

Author Comment

by:chtullu135
Comment Utility
Thanks everyone.  I've looked at Tony's autofeuploader but cost is an issue for the client.  Isn't it always. I'll try the vbscript approach.  The front end will be deployed as a accdr file since some of the users don't have MS Access, so the VBS approach looks like it will work best.  I'll post the results.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I use a batch file.  It overwrites the database each time.  You can get a little "smarter" if you use VBScript as Nick suggests and actually check the version before updating.
md c:\ClientMgt
del c:\ClientMgt\AOAClientManagement.accdr
copy \\newfiscal\Afox\AFox\AccessApplications\ClientMgt\AOAClientManagement.accdr c:\ClientMgt
c:\ClientMgt\AOAClientManagement.accdr

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@PatHartmann,

Mine's 120MB, so the copy is something we like to avoid in the name of haste!
Not included in the code was a check for _Backup.mdb -- which means the client has crashed it.
Then it gets cleaned up and a new copy downloaded.

Nick67
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
That's a pretty big FE.  Are you keeping tables there also?  Have you compacted recently?  I have a couple of substantial FE's and they're all less than 50MB.  I don't leave CR turned on automatically since once the FE is distributed it doesn't bloat but while I am working on it, it can bloat substantially so I have to remember to compact before I distribute.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
That's a pretty big FE. Yeah
Are you keeping tables there also? No
Have you compacted recently?  Religiously
Decompile probably monthly.

159 tables
496 Queries
170 forms & subforms
158 reports & subreports
50 modules.

It's a bit big :)
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I'm not sure that beats Tony Toews' database but it is definitely on the large side.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
What's Tony got on the go?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
It is vertical market system.  I think for the trucking industry.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
He's about 300 miles south of here, and I've seen his site over the years (His, Leban's, AllenBrowne, and the AccesWeb are ones anyone whose been at this business for a while have referred to repeatedly) but I've never seen any indications of size
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I only know about the size because when I was at a meeting with the Access team in Redmond a few years ago, they were discussing it.  Apparently, Tony gives them a copy to use for testing Access:)
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
LOL
I've tried to interest Redmond in mine because it certainly takes Access right to the limit.
(Reports with 450 digital photos in them, reports with 8 subreports --which is pretty much at the limit of what A report will do without corrupting the object)
No takers though.
You can't push it to SharePoint, or have an untrained person create stuff like that, so they're not interested.
I was in on the pre-2007 'experience feedback'  They were very high on a 50K limit for SharePoint lists as Access datasources--with no upgrade path.

The devs were much less excited about a macro-based platform without VBA with those kinds of limits.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:chtullu135
Comment Utility
Hello Nick,

I have a question with regards to the code you posted. As I understand it, the code that you labeled as used by the client, is placed in vbs script , while the first code that you posted is called by the client side code.  is that correct
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
No, both are VBS scripts.
The first VBS script is for MY use.
It knocks the version forward a point in a table and in the textfile and copies the development db to the deployment folder.  I can deploy with a double-click on that script.  Very handy.

The second is for the clients' use.  It checks the version number from the textfile in the deployment folder and the local folder and compares them.  If it's a mismatch, it copies from the deployment to the local folder and then opens it.  If not, it just opens the local copy immediately.
0
 

Author Comment

by:chtullu135
Comment Utility
Oh I see.  Thanks for the clarification Nick.
0
 

Author Comment

by:chtullu135
Comment Utility
Hello Nick,

When I run the script, I am getting the compilation error as shown in the attached screen shot.  From the error message, it appears to be a problem with an if statement syntax.  Below is the code as modified for my environment

'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\PathToDeploymentVersion.txt") then
    Set MyConn= fs.OpenTextFile("C:\Databases\PathToDeploymentVersion.txt",1, true)
    ServerSplit = Split(MyConn.readall, ".")
    MyConn.Close
end if

If fs.FileExists(PathToLocalVerDotTxt) then
    Set MyConn1= fs.OpenTextFile("C:\Databases\PathToLocalVersion.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\ProcurementOperations.accdb"
BuiltPath = "C:\LocalVersion\ProcurementOperations.accdb"
TempPath = "c:\prod\temp"

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

fs.copyfile userpath, BuiltPath, -1
fs.copyfile PathToDeployVerDotTxt, PathToLocalVerDotTxt, -1



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

Function CheckDeployment() 
Dim BuiltPath
BuiltPath = PathToLocalVersion

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



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

CloseCmdWindow()
dim PathToMDE
PathToMDE = PathToLocalVersion

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

Open in new window

0
 

Author Comment

by:chtullu135
Comment Utility
Hello Nick,

I found some errors in the code, that I am fixing (I forgot to set some of the paths).  After I fix them, I'll rerun the script
0
 

Author Comment

by:chtullu135
Comment Utility
Hello Nick,

I'm now getting a syntax error on line 146 located in the CloseWindow subprocedure

'declare an object to be a filesystem object
Public fs

'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\PathToDeploymentVersion.txt") then
    Set MyConn= fs.OpenTextFile("C:\Databases\PathToDeploymentVersion.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\PathToLocalVersion.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\ProcurementOperations.accdb"
BuiltPath = "C:\LocalVersion\ProcurementOperations.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\PathToDeploymentVersion.txt", "C:\Databases\PathToLocalVersion.txt", -1



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

Function CheckDeployment() 
Dim BuiltPath
BuiltPath = "C:\LocalVersion\ProcurementOperations.accdb"

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



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

CloseCmdWindow()
dim PathToMDE
PathToMDE = "C:\LocalVersion\ProcurementOperations.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'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\PathToDeploymentVersion.txt") then
    Set MyConn= fs.OpenTextFile("C:\Databases\PathToDeploymentVersion.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\PathToLocalVersion.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\ProcurementOperations.accdb"
BuiltPath = "C:\LocalVersion\ProcurementOperations.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\PathToDeploymentVersion.txt", "C:\Databases\PathToLocalVersion.txt", -1



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

Function CheckDeployment() 
Dim BuiltPath
BuiltPath = PathToLocalVersion

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



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

CloseCmdWindow()
dim PathToMDE
PathToMDE = PathToLocalVersion

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 Win64_Process where name='cmd.exe'" 
Set objQResult = oWmg.Execquery(strWndprs) 

For Each objProcess In objQResult 

	intReturn = objProcess.Terminate(1) 

Next'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\PathToDeploymentVersion.txt") then
    Set MyConn= fs.OpenTextFile("C:\Databases\PathToDeploymentVersion.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\PathToLocalVersion.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\ProcurementOperations.accdb"
BuiltPath = "C:\LocalVersion\ProcurementOperations.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\PathToDeploymentVersion.txt", "C:\Databases\PathToLocalVersion.txt", -1



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

Function CheckDeployment() 
Dim BuiltPath
BuiltPath = PathToLocalVersion

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



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

CloseCmdWindow()
dim PathToMDE
PathToMDE = "C:\Databases\LocalVersion\ProcurementOperations.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 Win64_Process where name='cmd.exe'" 
Set objQResult = oWmg.Execquery(strWndprs) 

For Each objProcess In objQResult 

	intReturn = objProcess.Terminate(1) 

Next

Open in new window

0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
You have multiples of the required code in the same code block.
You have two Main()
You have two sets of declarations.
Something's gone wrong
Line 145 begins the declarations for another script on the same line as the Next of 141's for each
--and my bad -- in clean up my production code for you, I lost an End Sub


This is your take on the client's script, I called it deploy.vbs
'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\PathToDeploymentVersion.txt") then
    Set MyConn= fs.OpenTextFile("C:\Databases\PathToDeploymentVersion.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\PathToLocalVersion.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\ProcurementOperations.accdb"
BuiltPath = "C:\LocalVersion\ProcurementOperations.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\PathToDeploymentVersion.txt", "C:\Databases\PathToLocalVersion.txt", -1



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

Function CheckDeployment() 
Dim BuiltPath
BuiltPath = "C:\LocalVersion\ProcurementOperations.accdb"

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



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

CloseCmdWindow()
dim PathToMDE
PathToMDE = "C:\LocalVersion\ProcurementOperations.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

0
 

Author Comment

by:chtullu135
Comment Utility
Right after I posted my reply, I found that I had multiple copies of the required code.  After fixing that, I had a compilation error on the last line, which you have just addressed. I'll rerun the code with the new changes.
0
 

Author Comment

by:chtullu135
Comment Utility
Hello Nick
I've rerun the code and am now getting a type mismatch error on line 49 (See attached screen shot)

'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\PathToDeploymentVersion.txt") then
    Set MyConn= fs.OpenTextFile("C:\Databases\PathToDeploymentVersion.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\PathToLocalVersion.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:\Databases\Deployment"
BuiltPath = "C:\Databases\LocalVersion"
TempPath = "c:\prod\temp"

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

fs.copyfile userpath, BuiltPath, -1
fs.copyfile "C:\Databases\PathToDeploymentVersion.txt", "C:\Databases\PathToLocalVersion.txt", -1



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

Function CheckDeployment() 
Dim BuiltPath
BuiltPath = "C:\Databases\LocalVersion\ProcurementOperations.accdb"

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



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

CloseCmdWindow()
dim PathToMDE
PathToMDE = "C:\Databases\LocalVersion\ProcurementOperations.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

0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Ok,

What's in C:\Databases\PathToLocalVersion.txt
My ver.txt has
2.0.1.488

It's expecting a string of numbers delimited by periods, and it will look at each integer in the split.
If the server version has a bigger number than the local, it's deployment time
0
 

Author Comment

by:chtullu135
Comment Utility
Here are my text files.  I thought that the text file would contain the actual paths to the databases.  Instead they just contain version numbers.  Is that correct?
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Instead they just contain version numbers.
Yup
Here are my text files.
Where?

I thought that the text file would contain the actual paths to the databases
Nope.
Those are in lines 65-66 of the last code window
Line 67 you will likely not need.
My app does a boatload of stuff where a dedicated temp directory for it makes sense.
0
 

Author Closing Comment

by:chtullu135
Comment Utility
Thanks Nick.  I got it working.  It's working great.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Now, you have to knock the convenient one for YOU to send the dev verison of the mdb to the deploy location & update ver.txt into shape.

In mine, I also have it check for FileName_Backup.mdb.
If that exists, the end-user crashed Access the last time out.
I then look for and delete the backup, the main, and an ldb if it exists.

Then I get it to pull a fresh copy.
Stops the phone calls of 'umm, it says to save it as _backup but that already exists.  What do I do now?"
0
 

Author Comment

by:chtullu135
Comment Utility
Stops the phone calls of 'umm, it says to save it as _backup but that already exists.  What do I do now?"
I've seen that happen
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
I've got this stuff in my deploy.vbs
Up in the head, I define all the variables
'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
'declare the deploy ver.txt location
Public DeployVerTxt
'declare the local ver.txt location
Public LocalVerTxt
'declare the local possible bad file location
Public LocalBad
'declare the local possible bad ldb file location
Public LocalBadLDB
'declare the local mdb

Open in new window


Up in Main() I check for the existence of LocalBad
LocalBad = "c:\SomePath\SomeFile_Backup.mdb"
if fs.FileExists(LocalBad) then
    Call KillBad()
    Call CopyUpdate()
    FireDB()
    Exit Sub
end if

Open in new window


And this kills the remnants of any crashes
'------------------------------------------------------------
Private sub KillBad()

If fs.FileExists(LocalBad) Then
    fs.DeleteFile LocalBad, True
end if

If fs.FileExists(LocalGood) Then
    fs.DeleteFile LocalGood, True
end if

If fs.FileExists(LocalBadLDB) Then
    fs.DeleteFile LocalBadLDB, True
end if

end sub

Open in new window

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now