Parameterize SQL Queries using R RODBC

The Input Parameter pop-up box is strictly an MSAccess.exe GUI feature. If running MS Access as a backend database (outside of the MS Office software) via ODBC, query with unknown parameter will fail and error raised on the script making ODBC call.

Therefore, you will need to create a similar GUI pop-up box in R for this need using libraries such as GWidgets or Shiny, then pass user's input value into query. And do so with actual parameterization using RODBCext (extension of RODBC) in case a malicious user runs SQL injection and potentially wipes data or destroys your SQL Server database.

Below is an example using GWidgets2 with a combo box for Fiscal Years (screenshot below).

Libraries

library(RDOBC)
library(RODBCext)
library(gWidgets2)
library(gWidgets2tcltk)

options(guiToolkit="tcltk")

GUI Function (create the R and SQLServer gif image beforehand)

mainWindow <- function(){

  # TOP OF WINDOW
  win <- gWidgets2::gwindow("Fiscal Year User Input", height = 200, width = 300)

  tbl <- glayout(cont=win, spacing = 8, expand=TRUE)

  # IMAGE
  tbl[1,1] <- gimage(filename = "RSQLServerGUI.gif", 
                     dirname = "/path/to/gif/image", container = tbl)
  # LABEL
  tbl[2,1] <- glabel("Fiscal Year Selection:                      ", container = tbl)
  font(tbl[2,1]) <- list(size=12, family="Arial")

  # COMBO BOX OF FISCAL YEARS
  tbl[3,1, expand=TRUE] <- fiscal_year_cbo <- gcombobox(as.character(c(2012:2018)), 
                                                        selected = 1, editable = TRUE, 
                                                        index=TRUE, container = tbl)
  font(tbl[3,1]) <- list(size=16, family="Arial")

  # COMBO BOX CHANGE FUNCTION (2012 - 2018)
  addHandlerChanged(fiscal_year_cbo, handler=function(...)  {
    fiscal_year_value <- svalue(fiscal_year_cbo)           # GET USER SELECTED VALUE
    gmessage(paste("You selected FY:", fiscal_year_value))

    degrees <- getDegreesData(fiscal_year_value)           # GET DATABASE DATA 
    dispose(win)                                           # CLOSE WINDOW
  })

}

Query Function (called in combo box change handler above)

getDegreesData <- function(fy_param) {

    dbhandle <- odbcDriverConnect('driver={SQL Server};server=SQLSERVER;database=MYDATABASE;trusted_connection=true')

    # PREPARED STATEMENT (NO CONCATENATED DATA)
    strSQL <- "select Inst, ID, DegreeDate, Degree 
               from DEGREE 
               where FY = ?
               group by Inst, ID, DegreeDate, Degree 
               order by Inst, ID, DegreeDate, Degree"

    # PASS PARAMETER TO RETURN DATAFRAME
    sql_df <- sqlExecute(dbhandle, strSQL, fy_param, fetch=TRUE)
    odbcClose(dbHandle)

    return(sql_df)
}

Run GUI

m <- mainWindow()

Screenshot