0

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

Improve this question

1 Answer 1

Reset to default
1

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.