Connect to MS Access DB

DBI
RODBC
R6 Class
Debug DBI Connection

Updated 15.09.2022

Before connecting to your Access DBMS via Windows OS, you can check if you have the MS Access driver installed in your machine by running

  ## List everything
  odbc::odbcListDrivers()

  ## You will get the results someting like this..
  ## ... truncated
  26               Microsoft Access Driver (*.mdb, *.accdb)        FileUsage
  27               Microsoft Access Driver (*.mdb, *.accdb)        FileExtns
  28               Microsoft Access Driver (*.mdb, *.accdb)         SQLLevel
  29 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)       UsageCount
  30 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)         APILevel
  31 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) ConnectFunctions

  ## List only the driver
  sort(unique(odbcListDrivers()[[1]]))

  [1] "Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)"
  [2] "Microsoft Access Driver (*.mdb, *.accdb)"
  [3] "Microsoft Access Text Driver (*.txt, *.csv)"
  [4] "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
  [5] "ODBC Driver 13 for SQL Server"
  [6] "SQL Server"
  [7] "SQL Server Native Client 11.0"

If you don’t get similar results then you have to set it up with this instruction. If you have trouble setting up Data Source Name (DSN) then you could try accessing the odbc driver by going to control panel -> administrative tools -> select data sources(ODBC) -> right click the file -> go to properties -> in the shortcut tab -> change the path from %windir%System32odbcad32.exe to %windir%SysWOW64odbcad32.exe. The reference is from here.

Several packages can be used to access your data in your DBMS. I have used RODBC and DBI. Here is some examples how I have used them to connect to MS Access DBMS.

DBI

You need both DBI and odbc packages. Basically odbc is the connecting bridge from your R codes to Access file and DBI is the guy who do the actual work. I have to use a complete .connection_string to make it works.

  pkg <- c("odbc", "DBI", "glue")
  sapply(pkg, require, character.only = TRUE)

  db_con <- "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="
  MDBPATH <- "C:\\enc\\DBtest\\STYRING\\KHELSA_dev.accdb"

  cs <- paste0(db_con, MDBPATH)
  con <- dbConnect(odbc::odbc(), .connection_string = cs)
  tblCols <- c("FILID","FILNAVN", "FORMAT")
  orgTb <- "ORIGINALFILER"
  sqq <- glue::glue_sql("SELECT TOP 5 {`tblCols`*}
                         FROM {`orgTb`}",
                        .con = DBI::ANSI())

  ## for interactive use dbGetquery
  DBI::dbGetQuery(con, sqq)

  ## dbSendQuery should use dbFetch and dbClearResult
  rq <- DBI::dbSendQuery(con, )
  DBI::dbFetch(rq)
  DBI::dbClearResult(rq)

  DBI::dbDisconnect(con)

RODBC

This is how I use this package.

  library(RODBC)
  ## Set up driver info and database path
  DRIVERINFO <- "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="

  ## accdb file
  MDBPATH <- "C:\\enc\\DBtest\\STYRING\\KHELSA_dev2.accdb"
  PATH <- paste0(DRIVERINFO, MDBPATH)

  ## Establish connection
  channel <- RODBC::odbcDriverConnect(PATH)

  ## Load data into R dataframe
  RODBC::sqlQuery(channel, "SELECT TOP 5 * FROM KH_DELER")
  RODBC::sqlFetch(channel, "KH_DELER", max = 5)

  ## Close and remove channel
  close(channel)
  rm(channel)

R6 Class

To implement R6 Class with connection to database could be basically done as follows:

  library(R6)

  HelseProfil <- R6::R6Class(
                       classname = "HelseProfil",
                       class = FALSE,
                       cloneable = FALSE,
                       public = list(
                         dbname = NULL,
                         dbconn = NULL,
                         initialize = function(dbname = NULL){
                           if (is.null(dbname)) return(message(">>> DB name is missing!"))
                           else {
                             self$dbname <- dbname
                           }
                         },
                         db_connect = function(){
                           stopifnot(!is.null(self$dbname))
                           cs <- paste0(private$..drv, self$dbname)
                           self$dbconn <- DBI::dbConnect(odbc::odbc(),
                                                         .connection_string = cs,
                                                         encoding = "latin1")
                         },
                         db_close = function(){
                           DBI::dbDisconnect(self$dbconn)
                         }
                       ),
                       private = list(
                         ..drv = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=",
                         finalize = function(){
                           DBI::dbDisconnect(self$dbconn)
                         }
                       )
                     )

Here I actively disable class and cloneable for speed which you can read from R& document.

Debug DBI Connection

Argument debug = TRUE can be used to debug and see the SQL code eg.

DBI::dbConnect(duckdb::duckdb(), file.path(duckFile), debug = TRUE)

database