Question about Calling procedures

J

Jim Evans

I have a task that requires the creation of a .bat File and then the
execution of that .bat file.

If I write a procedure that calls procedure 1 (to create the .bat file), can
I be certain that the file will be created and closed before I call
procedure 2 (to execute the .bat file)?

Currently, I use 2 buttons on a form to achieve this. After entering the
data into the form to mark certain records in a table, button 1 is clicked
and this creates the .bat file and reports the number of records affected by
a MsgBox. I then click button 2 to execute the .bat file and the Command
Prompt window opens and I can watch for success or failure of each command
in the batch file.

If I change the way that I accomplish this task and make it more efficient
by just calling a Function, can I be certain that the file will be created
and closed before the Function to execute the batch file is called?

There is no room for error in this. This entire process is either creating
or deleting Reservations for IP Addresses on 3 DHCP servers located on the
WAN.

Jim
 
D

Douglas J. Steele

How are you creating the batch file? If, for example, you're using the VBA
Print statement, yes, the file should be completely written when the routine
files.

What is in the batch file? There might be another way of achieving the same
results.
 
J

Jim Evans

The batch file is created by executing a vbs script.

The script queries the db and filds all records that are marked to be
updated (Reservation deleted and db record updated to reflect this). It then
writes a line to the batch file which calla the Net Dhcp command and sends
the remainder of the line to the Dhcp server to delete the Reservation. This
is repeated for each marked record and then repeated for each of the other 2
Dhcp servers we use.

I have been thinking about using the Print command to create the commands in
the batch file but have not ventured that far...saw the use of the Print
command to write to a text file the first time over this past weekend,
thanks to Stuart McCall!

Am I being clear and answering all of your questions?

Jim

How are you creating the batch file? If, for example, you're using the VBA
Print statement, yes, the file should be completely written when the routine
files.

What is in the batch file? There might be another way of achieving the same
results.
 
J

Jack Leach

I've seen people use DoEvents three times...

DoEvents: DoEvents: DoEvents

to ensure that enough time has passed and the system has processed the file
creation. For me this is not quite enough, I run a FileExists function after
that to check the file. The function I have uses GetAttr() and returns an
error if the file is not present, I pulled it off UtterAccess somewhere I
think... a google search should get it, I don't have it handy at the moment.

As Doug mentions though, you can probably execute the commands that are
contained in the batch file directly from vba, and if you really really
wanted to (it's not easy, but you can) you can get the return values from the
shelled commands returned to your vba environment.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
D

Douglas J. Steele

Sounds as though there's no reason you can't be creating the batch file
strictly using VBA, rather than using VBScript.

In the meantime, though, assuming you're running the VBScript by shelling to
CScript (or WScript), see http://www.mvps.org/access/api/api0004.htm at "The
Access Web" for how to waiting until the Shelled process has completed.

And even if you do that, in the second procedure, you can use the Dir
function to ensure that the desired file exists:

If Len(Dir("C:\Temp\MyBatch.bat")) = 0 Then
' Batch file does not exist
Else
' Batch file exists: run it.
End If
 
J

Jim Evans

Doug and Jack,

OK. Creating the proc in VBA was much easier than I expected. Changed 2
lines and it ran without a hiccup.

1. Set conn = CreateObject("ADODB.Connection")
to:
Set conn = CurrentProject.Connection

2. Commented out wScript.Sleep(2000)

It ran like a charm and created the file exactly as it should be.

That seems to have been the easy part, now I need to create the proc that
executes the .bat file. What command in that proc should I use to execute
the file?

Thanks much, you have both made my day. I have several other tasks that are
related and are currently using a combination of scripts via Macros that I
can now change. The reason to change them is when I distribute this db,
there is one user that I must also upload all new or changed .vbs or .bat
files to, a real PITA. If it is all included in yb VBA code, no more need to
do this updating. :)

Jim

"Jack Leach" <dymondjack at hot mail dot com> wrote in message
I've seen people use DoEvents three times...

DoEvents: DoEvents: DoEvents

to ensure that enough time has passed and the system has processed the file
creation. For me this is not quite enough, I run a FileExists function
after
that to check the file. The function I have uses GetAttr() and returns an
error if the file is not present, I pulled it off UtterAccess somewhere I
think... a google search should get it, I don't have it handy at the moment.

As Doug mentions though, you can probably execute the commands that are
contained in the batch file directly from vba, and if you really really
wanted to (it's not easy, but you can) you can get the return values from
the
shelled commands returned to your vba environment.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
D

Douglas J. Steele

Shell Environ$("COMSPEC") & " ""C:\Some Folder\MyBatch.bat"""

Environ$("COMSPEC") returns the name of the command line interpretter
(typically cmd.exe). After that is one double quote, a space, two double
quotes in a row, the full path to the batch file, then three double quotes
in a row. The reason for the odd double quotes to handle spaces in the path
to the file (as in my example). If you know that there will never be spaces
in the path to the batch file, you can get away with

Shell Environ$("COMSPEC") & " C:\Folder\MyBatch.bat"
 
J

Jim Evans

Doug,

Is there a way to send a command line switch, I like the Command window to
remain open (/K) so that I can get additional information about failed
commands in the .bat file?

Jim

Shell Environ$("COMSPEC") & " ""C:\Some Folder\MyBatch.bat"""

Environ$("COMSPEC") returns the name of the command line interpretter
(typically cmd.exe). After that is one double quote, a space, two double
quotes in a row, the full path to the batch file, then three double quotes
in a row. The reason for the odd double quotes to handle spaces in the path
to the file (as in my example). If you know that there will never be spaces
in the path to the batch file, you can get away with

Shell Environ$("COMSPEC") & " C:\Folder\MyBatch.bat"
 
D

Douglas J. Steele

I believe it's

Shell Environ$("COMSPEC") & " ""C:\Some Folder\MyBatch.bat"" /k"

or

Shell Environ$("COMSPEC") & " C:\Folder\MyBatch.bat /k"
 
J

Jim Evans

Thanks, Doug. I will see which works.

Jim

I believe it's

Shell Environ$("COMSPEC") & " ""C:\Some Folder\MyBatch.bat"" /k"

or

Shell Environ$("COMSPEC") & " C:\Folder\MyBatch.bat /k"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top