Problem with VBScript that imports Access database if new version

[Lynda]

Hello:

I hope someone can tell me what is wrong with a VBS program on my local
drive. The VBScript file checks to see if there is a newer version of my
Access database file located out on a shared network drive. If the version
number is higher than the database located on my C drive, then the vbs
script saves my current application as Savexxxx.mdb, imports the newer
application and then opens the newer version of the Access application. For
some reason, the vbs script is able to complete most of the commands except
for the final one where it opens up the newer database application.

This script worked just fine until recently (about around December 2008) and
then just stopped working properly. This VBS script works just fine for
everyone else in my office so I think that I am obviously the problem.
Something is blocking this from completing.

I am attaching the script below. Perhaps someone can tell me if there is
some setting that I need to fix on my local laptop. I am running Windows
XP, Microsoft Outlook connected to microsoft exchange server (the same as my
co-workers). As far as I can tell, there shouldn't be anything diferent for
me.

I hope someone can give me an answer. Here is the script.

Dim WshShell
dim Path
dim CurrentDirectory
dim MasterDirectory
dim OldVersionNum
dim NewVersionNum

Set WshShell = WScript.CreateObject("WScript.Shell")
set FSO = wscript.createobject("Scripting.FileSystemObject")
Set conn = Wscript.CreateObject("ADODB.Connection")
set rs = wscript.createobject("ADODB.recordset")


Path= WshShell.RegRead("HKCU\Software\VB and VBA Program Settings\UMF
Prospects\UMF Prospects\DataPath")
MasterDirectory = Left(path, InstrRev(path, "\"))
path = WScript.ScriptFullName
CurrentDirectory= Left(path, InstrRev(path, "\"))


conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.ConnectionString = "data source= " & currentdirectory & "pxprog.mdb"
conn.Open

rs.open "Version",conn
OldVersionNum = rs("VersionNumber")
rs.close

conn.close
conn.ConnectionString = "data source= " & MasterDirectory & "pxprog.mdb"
conn.Open
rs.open "Version",conn
NewVersionNum = rs("VersionNumber")

set rs = nothing
set conn = nothing

if NewVersionNum > OldVersionNum then
WScript.Echo "Newer Prospect Program Version is Available"
FSO.CopyFile CurrentDirectory & "pxprog.mdb", CurrentDirectory &
"SavepxProg.mdb", True 'true means overwrite
FSO.CopyFile MasterDirectory & "pxprog.mdb", CurrentDirectory &
"pxProg.mdb", True
end if

set FSO = nothing

WshShell.Run CurrentDirectory & "pxprog.mdb", 1
WScript.Sleep 300
WshShell.AppActivate "ACCESS"

set wshShell = nothing

 

[Albert D. Kallal]

I don't really see anything wrong here, and as you say for others this works
just fine.

Also, you have a big long post,but then only provide the sentence:

it don't work

The above is not a lot to go on. Perhaps you put considerable more messages
(WScript.Echo) to narrow down where in the script it fails/stops?

Another issue is that often the "close" time of JET can be quite long. I
notice in your code for that 1st mdb file, you execute a close on the
recordset (and then kill the connection. However, in the 2nd file you do
not close that recordset file (any reason for this????).

You have:

conn.ConnectionString = "data source= " & MasterDirectory & "pxprog.mdb"
conn.Open
rs.open "Version",conn
NewVersionNum = rs("VersionNumber")

set rs = nothing
set conn = nothing

I would suggest you at least try to be consistent for both mdb files. I
would throw in a rs.close in the above set of code (like you have for the
1st file).

The 2nd thing is that when a file is on a network there can be a longer time
for the file to close. I would throw in a small delay after the above...say
about .4 of a second. ( WScript.Sleep 400).

Also, I note that the script assumes access is running, and it not clear
where/when access was started. I would suggest that you exit access to
ensure that the front end part is closed. Also, not that ms-access can take
quite a while to exit (especially with options like compact/repair turned
on -- which I don't recommend). I would suggest that you have your access
code EXIT ms-access before you run the script. Ms-access may not have yet
closed the particular database in question and the delay in closing can be
long...

 

[Lynda]

I had inserted the whole script into my posting. Here it is again.. It
works right up to the last 4 lines and then nothing happens. What happened
previously was that it would automatically open my Access application
(pxprog.mdb).

So I was wondering if there is something stopping the script.
Dim WshShell
dim Path
dim CurrentDirectory
dim MasterDirectory
dim OldVersionNum
dim NewVersionNum

Set WshShell = WScript.CreateObject("WScript.Shell")
set FSO = wscript.createobject("Scripting.FileSystemObject")
Set conn = Wscript.CreateObject("ADODB.Connection")
set rs = wscript.createobject("ADODB.recordset")


Path= WshShell.RegRead("HKCU\Software\VB and VBA Program Settings\UMF
Prospects\UMF Prospects\DataPath")
MasterDirectory = Left(path, InstrRev(path, "\"))
path = WScript.ScriptFullName
CurrentDirectory= Left(path, InstrRev(path, "\"))


conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.ConnectionString = "data source= " & currentdirectory & "pxprog.mdb"
conn.Open

rs.open "Version",conn
OldVersionNum = rs("VersionNumber")
rs.close

conn.close
conn.ConnectionString = "data source= " & MasterDirectory & "pxprog.mdb"
conn.Open
rs.open "Version",conn
NewVersionNum = rs("VersionNumber")

set rs = nothing
set conn = nothing

if NewVersionNum > OldVersionNum then
WScript.Echo "Newer Prospect Program Version is Available"
FSO.CopyFile CurrentDirectory & "pxprog.mdb", CurrentDirectory &
"SavepxProg.mdb", True 'true means overwrite
FSO.CopyFile MasterDirectory & "pxprog.mdb", CurrentDirectory &
"pxProg.mdb", True
end if

set FSO = nothing

WshShell.Run CurrentDirectory & "pxprog.mdb", 1
WScript.Sleep 300
WshShell.AppActivate "ACCESS"

set wshShell = nothing

 

[Albert D. Kallal]

ok..so, it part that launches ms-access...

Try putting in a delay of about 1 second right after you set FSO = nothing
and right before you launch ms-access.

See if above helps. It is possible that the file not yet quite been finished
being copied (and the os not yet released the file locks on that file).
There can be a bit of a delay....

I also notice in your repost you not added the close recordset command as I
suggested. It may not help, but it is just a good idea to be consistent and
close both mdb files in the same fashion...

As mentioned, also check/ensure that ms-access is not already running.