Executing R script from command button in Microsoft Access and passing data from Microsoft Access to R

Question

I would like to send a query to R and execute R script from a command button in Microsoft Access. Has anyone done this before and can suggest how to implement this?

Data are stored in Microsoft Access and the query is based on a button the user clicks on in Microsoft Access.

Thanks

score 2 · Accepted Answer · edited Mar 20 '17 at 10:04

Simply call a Shell command using the automated Rscript.exe. However first, you may need to export your query data into a format R can read such as csv or txt file. Alternatively, have R connect to database via RODBC.

In the command line call you can send parameters such as query name (for RODBC SQL statement) or csv/txt path for R to receive by concatenating the shell string with space-separated values. Then use commandArgs() list in R to receive values.

Also, double quotes are needed if path names have any space. And if you have Rscript in your environmental variable, PATH, you can use the Rscript command directly. Otherwise, enter full path where Rscript.exe resides (usually in installation bin folder). Examples commented out:

Private Sub QueryCmdButton_Click()
  DoCmd.TransferText acExportDelim, , "qryToExport", "C:\Path\To\CSV.csv"

  Shell "Rscript ""C:\Path\To\R\script.R""", vbNormalFocus
  ' Shell "C:\Path\To\Rscript.exe ""C:\Path\To\R\script.R""", vbNormalFocus    
  ' Shell "Rscript ""C:\Path\To\R\script.R""" & " " & qryName & " " & csvPath, vbNormalFocus

  MsgBox "Successfully processed R script!", vbInformation
End Sub

Or a more sophisticated Shell call to receive a return code for error handling:

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

   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

Sorry for previous annoying comments. I couldn’t find any good documentation about this method but you seem to figure this out. I was able to get the "simple" version of your code to work with an exception for one detail please see. http://stackoverflow.com/questions/40538004/prombelm-with-r-script-execution-from-access-2016-command-button Also, I’ve tried substituting the path into the “fancy” version of your code but I get an error message each time, not sure why? Thank you for your help — user3781528, Nov 11 '16 at 15:03

score 0 · Answer 2 · edited May 23 '17 at 11:46

You can run R code from Microsoft Access using VBA, see https://www.r-bloggers.com/a-million-ways-to-connect-r-and-excel/

To create a command button for your VBA code, see https://social.technet.microsoft.com/Forums/office/en-US/d3640d72-e91f-4d86-a52e-6d3a66888bf8/how-to-create-a-button-in-access-2010-that-runs-vba-code?forum=officesetupdeployprevious

In your R code, you can access Microsoft Access database using ODBC, see http://rprogramming.net/connect-to-ms-access-in-r/

If you have issue with 64-bit Windows and R, see How to connect R with Access database in 64-bit Window?

Executing R script from command button in Microsoft Access and passing data from Microsoft Access to R

2 Answers2