Shell function

[Andy]

A couple of questions about Shell functions.

1. When calling the Shell function, is it possible to pause the Access
script until the application called by the shell function has completed. The
shell function returns a double value which I guess is the process id of the
application that has been called?

2. Not strictly an Access VBA question but someone might know. In the Shell
function above I am calling a cmd script to copy an Access database from one
location to another (I then refresh all the linked tables in database at new
location - you can now see why I want to wait until the copy has completed -
hence question 1). When using copy or xcopy in DOS you need to cd to the
source directory (folder) and then copy a file (or files) to a target
directory (folder). Fine if the source directory has a local Drive letter but
how would you do this if the source directory is on the network with no local
drive letter (say \\machine1\folderA\). So:-

For Local Drive letter (say e the cmd script might look like :-

e:
cd folderA\
xcopy *.mde c:\myfolder\ /Y

For Nework :-

You cant do the e: bit to position on the selected drive.

Maybe the ip address of the Network machine could be used somehow?

Thanks.

 

[Douglas J. Steele]

See http://www.mvps.org/access/api/api0004.htm at "The Access Web" for one
way to accomplish #1.

As far as I'm aware, there's no need to use e: and cd in your example: this
should work just as well:

xcopy e:\folderA\*.mde c:\myfolder\ /Y

When using unmapped shares, this should work:

xcopy \\machine1\folderA\*.mde c:\myfolder /Y

 

[Andy]

Thanks again Doug - you were absolutely correct with the xcopy not needing
the cd steps. I haven't tried with network path yet but am more confident
that it will work - I will try tomorrow.

On the Shell Wait routine - I have added code into a new module.

My code did have :-

Shellstr = "d:\ajr dev\al\alcf\alcopy97-all2.cmd"
Shell(Shellstr)

and now has :-

Shellstr = "d:\ajr dev\al\alcf\alcopy97-all2.cmd"
Call ShellWait(Shellstr)

Trouble is, the Access script now just hangs indefinately. Am I calling the
new sub incorrectly?

--------

 

[John Nurick]

IIRC you need to explicitly call cmd.exe with the /C switch to make the
process terminate when the script finishes, e.g.

Shellstr = "cmd.exe /C "" d:\ajr dev\al\alcf\alcopy97-all2.cmd"""

 

[Andy]

John/Doug - Thank you both it works fine now.

Just for the benefit of others reading this trail - I have developed a
distribution facility for Access databases which are split on the network. It
is not good practice for Users to open Front End databases from the network
as Performance is affected and the Front End database may become corrupted if
the network connection is lost.

The design uses a stub database with exactly the same name as the Front End
database so if anyone tries to use the Front End on the network they open
this one instead. The stub databse copies the real Front End from a central
network location to their C drive and then refreshes the links to their
particular backend database.

Andy.

 

[Douglas J. Steele]

Yeah, I forgot that Andy mentioned it was a batch file.

Just for the sake of completeness, whether you use cmd.exe or command.exe
depends on the version of Windows you're running.

To be throrough, you should really find out the value of the ComSpec
environment variable:

Shellstr = Chr$(34) & Environ$("ComSpec") & Chr$(34) & _
"/C " & Chr$(34) & d:\ajr dev\al\alcf\alcopy97-all2.cmd & Chr$(34)

 

[Andy]

Thanks again Doug - just the quotes missing around the batch file path :-

Shellstr = Chr$(34) & Environ$("ComSpec") & Chr$(34) & _
"/C " & Chr$(34) & "d:\ajr dev\al\alcf\alcopy97-all2.cmd" & Chr$(34)

What does ComSpec env var tell us and what versions of Windows does it apply
to?

----

 

[Douglas J. Steele]

Good catch: the perils of cut-and-paste! <g>

ComSpec is an environment variable that specifies the command-line
processor. As far as I'm aware, it's applicable to all 32-bit Windows
operating systems. It handles the fact that for Win95 and Win98, it's
command.exe, whereas for Win2000 and WinXP it's cmd.exe (don't remember what
it is for WinNT and WinME, which is part of its usefulness! <g>)

-
Doug Steele, Microsoft Access MVP

(no private e-mails, please)