How to execute a Query inside MS Access using RODBC in R?

Instead of writing my own query, I want to use a saved query inside an MS Access Database, run that, then extract the output to a data frame in R. Is there a way to do this in RODBC?

This post is exactly what I need but for RODBC: How to execute query saved in MS Access using pyodbc

I have worked with RODBC before and you should be able to do just this. Some steps for you to follow based on my experience:

1. Set up the queries that you need in your access database.
2. Set up the ODBC connection on your PC to the access database.
3. Write the R script that will use the RODBC package to connect to the access database (via the ODBC connection from 2) and execute the scripts that you set up in point 1 above.

You can dispense with point 1 above if you want to hardcode the sql in the R script (similar to the python example you gave). From my experience, it's usually more robust to write your SQL as a stored procedure or view though. Doing so allows you to test your SQL on the database before you start working in your R environment.

If you follow these steps, then your R code should be something like:

conn <- odbcConnect(dsn="MyOdbcConnectionName", uid="myDatabaseUserName", pwd="myPassword") 
query <- "YourQuery goes here" 
# e.g. "select * from table"
# e.g. "EXEC myStoredProcedure"
data <- sqlQuery(conn, query)
close(conn)