Running an external script from Access

Question

Hi all,

I want my Access application to run an external program (in this case a R script) after the user clicks a button. I use this code:

Dim RetVal
RetVal = Shell("""C:\Program Files\R\R-2.10.1\bin\R.exe"" CMD BATCH --no-environ --silent --no-restore --no-save ""c:\test.R"" ""c:\test-result.txt""", vbHide)
MsgBox RetVal

This works fine. But:

• The Access code goes on while my script is running. How can I make Access waiting for the script finishing?
• Has anybody suggestions about how to give an error message of the script back to Access?

Thanks in advance.

Answer 1

The OpenProcess and WaitForSingleObject combo that @Remou links to, is probably your best bet for doing this. You should take a look at this, it's a nice drop in module for shell and wait.

For returning a message back from the script, you could mess around with redirecting the scripts input and output. This is not for the faint of heart. As an alternative I would redirect the output of the script to a text file, then read in that file after it exits.