Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Running R script from Access module

Status
Not open for further replies.

rccline

Technical User
Jun 13, 2002
341
US
I would like to run an R script I have named "iterate.R" from an MS Access module via a command button on a form.

I found the following code on StackOverflow, but something is wrong when I substitute my path.

Link

Code:
Sub RunRscript()
   Dim shell As Object
   Dim path As String
   Dim waitTillComplete As Boolean: waitTillComplete = True
   Dim style As Integer: style = 1
   Dim errorCode As Integer

 '  comment out:  DoCmd.TransferText acExportDelim, , "qryToExport", "C:\Path\To\CSV.csv"

   Set shell = VBA.CreateObject("WScript.Shell")

   path = "RScript ""C:\Path\To\R\script.R"""    
   errorCode = shell.Run(path, style, waitTillComplete)

   Set shell = Nothing
End Sub


Thank you.

 
but something is wrong" - any errors?

If you want to put double quotes around "C:\Path\To\R\script.R", you may try:
[tt]path = "RScript "[blue] & Chr(34) & [/blue]"C:\Path\To\R\script.R" [blue]& Chr(34)[/blue]
Debug.Print path
[/tt]
You get:[tt]
RScript "C:\Path\To\R\script.R"[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
I think we need to see the code you are actually using, rather than the example you are trying to follow
 
Code:
CODE --> VBA
Sub RunRscript()
   Dim shell As Object
   Dim path As String
   Dim waitTillComplete As Boolean: waitTillComplete = True
   Dim style As Integer: style = 1
   Dim errorCode As Integer

 '  comment out:  DoCmd.TransferText acExportDelim, , "qryToExport", "C:\Path\To\CSV.csv"

   Set shell = VBA.CreateObject("WScript.Shell")

   path = "RScript ""C:\Path\To\R\script.R"""    
   [red]Debug.print DIR(path) 'This should put the file name of your R Script in the immediate window if it exists[/red]

   errorCode = shell.Run(path, style, waitTillComplete)

   Set shell = Nothing
End Sub
 
I created a form with a command button to run on click:

I am getting an error:

error_1_lylwyu.jpg


Code:
Private Sub Command5_LostFocus()
' Sub RunRscript()
   Dim shell As Object
   Dim path As String
   Dim waitTillComplete As Boolean: waitTillComplete = True
   Dim style As Integer: style = 1
   Dim errorCode As Integer

 '  comment out:  DoCmd.TransferText acExportDelim, , "qryToExport", "C:\Path\To\CSV.csv"

   Set shell = VBA.CreateObject("WScript.Shell")

   path = "RScript ""F:\D_Documents\GitHub-DasRotRad\access_run_script\r_function.R"""
   Debug.Print Dir(path) 'This should put the file name of your R Script in the immediate window if it exists

   errorCode = shell.Run(path, style, waitTillComplete)

   Set shell = Nothing
End Sub

Private Sub Detail_Click()

End Sub

R script:
Code:
r_function <- function(a) {
  for(i in 1:a) {
    b <- i^2
    print(b)
  }
}

r_function(5)
 
I am getting an error" on which line of code?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
The error occurs on Debut.Print Dir(path)
Error 52: Bad file name or number
 
>Debug.Print Dir(path) 'This should put the file name of your R Script in the immediate window if it exists

No, it does not, since path doesn't actually cvontain a path at this point; it containes a command

>Error 52: Bad file name or number
See above

This can be fixed by replacing

Code:
path = "RScript ""F:\D_Documents\GitHub-DasRotRad\access_run_script\r_function.R"""
Debug.Print Dir(path) [COLOR=green]'This should put the file name of your R Script in the immediate window if it exists[/color]

with

Code:
path = "F:\D_Documents\GitHub-DasRotRad\access_run_script\r_function.R"
Debug.Print Dir(path) [COLOR=green]'This should put the file name of your R Script in the immediate window if it exists[/color]
Path = "RScript """ & path & """

 
>a command button to run on click:

The code you have showed us is not the Click event code

Indeed, your reported error message "[tt]You didn't specify Search criteria [][/tt]" suggests that you have an (incomplete) macro bound to the button in question, which takes precedence over the Click event

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top