How to Connect to an MS Access database in R
This page will show you how to connect to a MS Access database in R and return data. This requires the package RODBC.
Note that this only works with 32 bit Windows. Try odbcDriverConnect through RODBC for 64 bit (sorry, you’ll have to find instructions elsewhere).
Note that the examples on this page show how to connect to Access using SQL. The SQL is within the double quotes in the “get data” step.
First, load the RODBC package (you’ll also have to install it if you don’t have it already).
# Load RODBC package library(RODBC)
Next, connect to the Access database. This code creates an object called “channel” that tells R where the Access database is.
- If you paste the path from windows be sure to change every backslash to a forward slash.
- Do not include the file extension (.accdb or .mdb) on the end of the name of the database.
# Connect to Access db
channel <- odbcConnectAccess("C:/Documents/Name_Of_My_Access_Database")
Finally, run a SQL query to return the data.
# Get data
data <- sqlQuery( channel , paste ("select *
from Name_of_table_in_my_database"))
A more complicated example specifying fields
- With the Access 2003 db I tested on I had to put brackets around any names of fields that included spaces (example: “my field” but if I put brackets around the name of a field without spaces (to rename it within the query) the query wouldn’t run. Anyone know why this is? This problem can best be avoided by using * to return all fields if you don’t have too much data as in the above example.
# Get data
data <- sqlQuery( channel , paste ("select Year,
[Name of Student],
Identification
from Name_of_table_in_my_database"))
For even more complicated examples of SQL queries, including queries using parameters from R, see http://rprogramming.net/connect-to-database-in-r/.
There are other options that can be used with RODBC. See official R-manual page on read.csv to learn more: http://cran.r-project.org/web/packages/RODBC/index.html.
Practice
To practice getting data from a database with R, try the exercises in this importing data into R tutorial.
Thanks for reading! This website took a great deal of time to create. If it was helpful to you, please show it by sharing with friends, liking, or tweeting! If you have any thoughts regarding this R code please post in the comments.