ExecuteFile hangs on .MAM error message

[bprice]

ExecuteFile seems to stop execution of the macro only if a .MAM error message appears. I can't close the error window with the macro because the macro stops. What am I doing wrong?

When I double click on a .MAM file, it starts, opens the database, creates reports and exits without error. When I run the .MAM from a macro, it sometimes gives an error, “x.MAM: Access to the device or file is denied". When that error occurs, the macro hangs, but the .MAM file completes and the Access window closes. The log file shows that, when the error occurs, no further macro commands are executed after the ExecuteFile>, so I can't try to detect the error window and close it. The error can occur at any time during the .MAM file's one minute execution.
When the error does not occur, execution proceeds immediately after the ExecuteFile>.
Here is a small example program that loops and eventually hangs:

Code: Select all

Let>k=0

Repeat>k

               Let>k=k+1
			   DeleteFile>N:\Nightly\*.snp
			   Wait>2
			   ExecuteFile>N:\WeeklyAccessReports\MacroAllNightlyReports.MAM
			   Wait>2
			   Wait>1
			   WaitWindowClosed>Microsoft Access*
			   Wait>10

Until>k,20

Here is part of the log showing one success, followed by one failure.

Code: Select all

3/6/2008 16:50:18:102 -   END: Repeat>k
3/6/2008 16:50:18:102 - START: Repeat>k
3/6/2008 16:50:18:102 -   END: Repeat>k
3/6/2008 16:50:18:102 - START: 
3/6/2008 16:50:18:102 -   END: 
3/6/2008 16:50:18:102 - START: Let>k=k+1
3/6/2008 16:50:18:102 -   END: Let>k=k+1
3/6/2008 16:50:18:102 - START: DeleteFile>N:\Nightly\*.snp
3/6/2008 16:50:18:118 -   END: DeleteFile>N:\Nightly\*.snp
3/6/2008 16:50:18:118 - START: Wait>2
3/6/2008 16:50:20:118 -   END: Wait>2
3/6/2008 16:50:20:118 - START: ExecuteFile>N:\WeeklyAccessReports\MacroAllNightlyReports.MAM

It hung here, so I manually closed the error window and it continued.

3/6/2008 17:14:58:399 -   END: ExecuteFile>N:\WeeklyAccessReports\MacroAllNightlyReports.MAM
3/6/2008 17:14:58:399 - START: Wait>2
3/6/2008 17:15:00:399 -   END: Wait>2
3/6/2008 17:15:00:399 - START: Wait>1
3/6/2008 17:15:01:399 -   END: Wait>1
3/6/2008 17:15:01:399 - START: WaitWindowClosed>Microsoft Access*
3/6/2008 17:15:01:399 -   END: WaitWindowClosed>Microsoft Access*
3/6/2008 17:15:01:399 - START: Wait>10
3/6/2008 17:15:11:399 -   END: Wait>10
3/6/2008 17:15:11:399 - START: 
3/6/2008 17:15:11:399 -   END: 
3/6/2008 17:15:11:399 - START: Until>k,20
3/6/2008 17:15:11:399 -   END: Repeat>k
3/6/2008 17:15:11:399 - START: Repeat>k
3/6/2008 17:15:11:399 -   END: Repeat>k
3/6/2008 17:15:11:399 - START: 
3/6/2008 17:15:11:399 -   END: 
3/6/2008 17:15:11:399 - START: Let>k=k+1
3/6/2008 17:15:11:399 -   END: Let>k=k+1
3/6/2008 17:15:11:399 - START: DeleteFile>N:\Nightly\*.snp
3/6/2008 17:15:11:414 -   END: DeleteFile>N:\Nightly\*.snp
3/6/2008 17:15:11:414 - START: Wait>2
3/6/2008 17:15:13:414 -   END: Wait>2
3/6/2008 17:15:13:414 - START: ExecuteFile>N:\WeeklyAccessReports\MacroAllNightlyReports.MAM
3/6/2008 17:16:08:133 -   END: ExecuteFile>N:\WeeklyAccessReports\MacroAllNightlyReports.MAM
3/6/2008 17:16:08:133 - START: Wait>2
3/6/2008 17:16:10:133 -   END: Wait>2
3/6/2008 17:16:10:133 - START: Wait>1
3/6/2008 17:16:11:133 -   END: Wait>1
3/6/2008 17:16:11:133 - START: WaitWindowClosed>Microsoft Access*
3/6/2008 17:16:41:821 -   END: WaitWindowClosed>Microsoft Access*

I aborted the macro here

3/6/2008 17:16:41:821 - Finished Macro : testrun

[Bob Hansen]

Try using RunProgram> vs. Execute.

As you noted, using Execute> continues to run the macro file.
Using Run Program with Let>RP_WAIT=1 allows the executable program to run until it is closed, and then the macro continues.

So you would have something like this:
Let>RP_WAIT=1
Run Program>N:\WeeklyAccessReports\MacroAllNightlyReports.MAM

---------------------
You may also be able to eliminate the Wait and WaitWindowClosed commands, since nothing will be happening until the MAM program is closed.

You may also find the Log file easier to read if you choose to just report the line before it is executed instead of before and after.

[bprice]

I had tried to use RunProgram, but got errors. I can't run it directly since it is a .MAM file and if I use "RunProgram>MSACCESS.EXE x.MAM" it gives errors on exit, claiming it was not expecting to exit that way.

I still liked your idea with RP_WAIT=1, so I tried it with ExecuteFile.
ExecuteFile seems to obey the RP_WAIT variable just like RunProgram. It bothers me that the manual does not state that explicitly. I hope I am not using a non-documented feature that could disappear in the next rev.

Thanks, Bob, this may work. Unfortunately, it is hard to test. My small example loop program, now with RP_WAIT=1 and no Waiting for windows, gives new errors. Now, somehow, the Delete command doesn't always delete all the files.
I will try the technique in the "production" macro, which doesn't loop, just runs once a day. It always fails in the morning, and is less likely to fail the rest of the day, making debug difficult.

[Marcus Tettmar]

RP_WAIT does *NOT* have any effect ExecuteFile. It is not documented under ExecuteFile because it has nothing to do with ExecuteFile. It is a Run Program directive.

I suspect your loop is looping too fast before the ExecuteFile has had a chance to finish and therefore could be trying to execute somethng that is already running. The deletes may be failing because the app is still open when the delete is tried.

You need to make the script wait until the MAM process has completed before it continues.

.MAM files are opened with Microsoft Access using the following shell:

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /NOSTARTUP /SHELLSYSTEM [ShellOpenMacro "%1"]

Where %1 is the MAM file.

So you could try:

Let>RP_WAIT=1
Run>"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /NOSTARTUP /SHELLSYSTEM [ShellOpenMacro "N:\WeeklyAccessReports\MacroAllNightlyReports.MAM"]

Modify the path to MSAccess accordingly for your system.

So:

Code: Select all

Let>k=0
Repeat>k
  Let>k=k+1
  DeleteFile>N:\Nightly\*.snp
  Wait>2
  Let>RP_WAIT=1
  Run>"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /NOSTARTUP /SHELLSYSTEM [ShellOpenMacro "N:\WeeklyAccessReports\MacroAllNightlyReports.MAM"]
Until>k,20

So this will now run msaccess, open your .MAM file and not proceed until access completes the operation. It will not loop until each MAM file is finished executing.

[bprice]

OK, I must have misinterpreted the log, ExecuteFile is apparently not affected by RP_WAIT.

The Run shell command works great. Now the .MAM never seems to error, (even in the production code), so I no longer have to worry about closing that intermittant error message.

Thanks!