Connection from Access to R via DBI

Question

I am looking to analyse data in R (using dplyr) contained in an Access database on my laptop. (My first time trying to set up a database connection in R.)

Looking at the tidyverse site, for dplyr to work on the Access data, it seems that the connection must be via the DBI package (rather than RODBC).

I'm struggling with the syntax of dbConnect.

My code for RODBC was

base1<-odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=[filepath]/AdventureWorks DW 2012.accdb")

My (failed) attempt for DBI is

DB <- dbConnect(drv=Microsoft Access Driver (*.mdb, *.accdb)), host=[filepath]/AdventureWorks DW 2012.accdb)

What am I doing wrong?

(I'm working on Windows 10 - everything 64 bit.)

Answer 1

I recently needed to convert my RODBC defined db connections to equivalent DBI connections. Here's the original RODBC function:

connect_to_access_rodbc <- function(db_file_path) {
   require(RODBC)
   # make sure that the file exists before attempting to connect
   if (!file.exists(db_file_path)) {
     stop("DB file does not exist at ", db_file_path)
   } 

   # Assemble connection strings
   dbq_string <- paste0("DBQ=", db_file_path)
   driver_string <- "Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
   db_connect_string <- paste0(driver_string, dbq_string)

   myconn <- odbcDriverConnect(db_connect_string)

   return(myconn)
}

As explained here, the dbplyr package is built from the DBI package. The first argument of the DBI::dbConnect() must be an appropriate back-end driver. See the link for a list of drivers. For Access, the odbc::odbc() driver is suitable. The second argument the dbConnect function is the full connection string as used in the previous odbcDriverConnect call. With that in mind the following function should connect to your access database:

connect_to_access_dbi <- function(db_file_path)  {
  require(DBI)
  # make sure that the file exists before attempting to connect
   if (!file.exists(db_file_path)) {
     stop("DB file does not exist at ", db_file_path)
   }
  # Assemble connection strings
  dbq_string <- paste0("DBQ=", db_file_path)
  driver_string <- "Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
  db_connect_string <- paste0(driver_string, dbq_string)

  myconn <- dbConnect(odbc::odbc(),
                      .connection_string = db_connect_string)
  return(myconn)
}

The odbc package documentation presents a more nuanced example as well: https://github.com/r-dbi/odbc#odbc