Running an external script from Access

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 = S开发者_StackOverflow中文版hell("""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 VBA code keeps on running while my script is executed. How can I make Access waiting for the script to be finished? Has anybody suggestions about how to give an error message of the script back to Access?

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.