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.)

Unsung

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

Bensen

Just a heads up for anyone seeing this in the future. You need the absolute path of the Access file for db_file_path. – Mambo

Answer 2

As explained here, you can still use odbcConnectAccess2007() but need 32-bit MS Access 2007 drivers from here. This worked great for me.

Pothead

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Velasquez

Answer 3

I just used this a couple days ago.

library(RODBC)

# for 32 bit windows
# Connect to Access db
# channel <- odbcConnectAccess("C:/Users/Excel/Desktop/Coding/Microsoft Access/Northwind.mdb")

# Get data
# data <- sqlQuery( channel , paste ("select * from Name_of_table_in_my_database"))


# for 64 bit windows
channel <- odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:/Users/Excel/Desktop/Coding/Microsoft Access/Northwind.mdb")

data <- sqlQuery( channel , paste ("select * from CUSTOMERS"))

odbcCloseAll()

Smile

Unfortunately, in order to be able to use dplyrI (apparently), I need to make the connection via DBI. – Unsung

dplyr. If I'm wrong - can someone help with that? - then I'm good to go. But dbplyr.tidyverse.org/articles/dbplyr.html is pretty specific – Unsung

Looking further at the DBI package notes, I see I need to define the driver in function dbDriver - I tried this using the Access driver name above (both with and without quotes) and got error message 'couldn't find driver' – Unsung