Shell and wait with msaccess.exe won't work

[padinka]

I have a access database (a report scheduling application) that cycles through a recordset and opens various access databases on our server created by other developers and runs reports from them on specific dates. My problem is that shellandwait won't work since the calling application is Access. I need to wait until the report has completed before executing another report or all of the reports for that day/time will try to run at the same time.

When using the FAQ shellandwait, I never get a message box using this code:

Dim retval As Long
retval = ExecCmd("C:\Program Files\Microsoft Office\Office\msaccess.exe " & rst!FileName & " /x " & rst!MacroName)
MsgBox "Process Finished, Exit Code " & retval

Any suggestions?
Trisha
padinka@yahoo.com

 

[SarahG]

If this is your exact code - check your quotes. You need extra quotes around the name of the access executable - or else use the DOS names e.g C:\PROGRA~1\ etc.

 

[padinka]

Thanks for the offer of help. That line works and the code executes but never returns control back to execute the next line of code.
Trisha
padinka@yahoo.com

 

[chrissie1]

try the code on this page its long and does an api call
and it works for other programs

http://www.mvps.org/access/api/api0004.htm

"What a wonderfull world" - Louis armstrong

 

[padinka]

That's pretty much the code that is used in the FAQ shell and wait but it won't work if it's launched from access and used to launch access.

Anyone else have any ideas other than the api kernel 32 stuff? Or an idea on how to force the kernel 32 to work calling the same application that is running the code?
Trisha
padinka@yahoo.com

 

[padinka]

I'm going to go with this


Dim TaskID As Double

'Run application and return Process ID (TaskID)
TaskID = Shell("C:\Program Files\Microsoft Office\Office\msaccess.exe " & rst!FileName & " /x " & rst!ModuleName, vbNormalFocus)
DoEvents

While TaskExists(TaskID)

'Wait for TaskExists False
Wend
Trisha
padinka@yahoo.com