How to connect R with Access database in 64-bit Window?

When I tried to connect R with Access database I get an error

odbcConnectAccess is only usable with 32-bit Windows

Does anyone has an idea how to solve this?

library(RODBC) 
mdbConnect<-odbcConnectAccess("D:/SampleDB1/sampleDB1.mdb")

Solution 1:

Use odbcDriverConnect instead. If you have 64-bit R installed, you may have to use the 32-bit R build.

odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:/SampleDB1/sampleDB1.mdb")

Solution 2:

Here is a single function that will transfer data from 32 bit access to 64 bit R without having to save any files. The function builds an expression string that is passed to a second 32 bit session; data is then returned to the original session using socket server package (svSocket). One thing to note is that the socket server saves the access data in the global environment so the second parameter is used to define the output instead of using "<-" to save the output.

access_query_32 <- function(db_table = "qryData_RM", table_out = "data_access") {
  library(svSocket)

  # variables to make values uniform
  sock_port <- 8642L
  sock_con <- "sv_con"
  ODBC_con <- "a32_con"
  db_path <- "~/path/to/access.accdb"

  if (file.exists(db_path)) {

    # build ODBC string
    ODBC_str <- local({
      s <- list()
      s$path <- paste0("DBQ=", gsub("(/|\\\\)+", "/", path.expand(db_path)))
      s$driver <- "Driver={Microsoft Access Driver (*.mdb, *.accdb)}"
      s$threads <- "Threads=4"
      s$buffer <- "MaxBufferSize=4096"
      s$timeout <- "PageTimeout=5"
      paste(s, collapse=";")
    })

    # start socket server to transfer data to 32 bit session
    startSocketServer(port=sock_port, server.name="access_query_32", local=TRUE)

    # build expression to pass to 32 bit R session
    expr <- "library(svSocket)"
    expr <- c(expr, "library(RODBC)")
    expr <- c(expr, sprintf("%s <- odbcDriverConnect('%s')", ODBC_con, ODBC_str))
    expr <- c(expr, sprintf("if('%1$s' %%in%% sqlTables(%2$s)$TABLE_NAME) {%1$s <- sqlFetch(%2$s, '%1$s')} else {%1$s <- 'table %1$s not found'}", db_table, ODBC_con))
    expr <- c(expr, sprintf("%s <- socketConnection(port=%i)", sock_con, sock_port))
    expr <- c(expr, sprintf("evalServer(%s, %s, %s)", sock_con, table_out, db_table))
    expr <- c(expr, "odbcCloseAll()")
    expr <- c(expr, sprintf("close(%s)", sock_con))
    expr <- paste(expr, collapse=";")

    # launch 32 bit R session and run expressions
    prog <- file.path(R.home(), "bin", "i386", "Rscript.exe")
    system2(prog, args=c("-e", shQuote(expr)), stdout=NULL, wait=TRUE, invisible=TRUE)

    # stop socket server
    stopSocketServer(port=sock_port)

    # display table fields
    message("retrieved: ", table_out, " - ", paste(colnames(get(table_out)), collapse=", "))
  } else {
    warning("database not found: ", db_path)
  }
}

Occasionally this function will return an error, but it does not impact data retrieval and appears to result from closing the socket server connection.

There is likely room for improvement, but this provides a simple and quick method to pull data into R from 32 bit access.

Solution 3:

Did not succeed with the given answers, but here is the step by step approach that eventually did the trick for me. Have Windows 8 on 64 bit. With 64 and 32 bit R installed. My Access is 32 bit.

Steps to use, assuming 32 bit Access on windows 8

Select 32 bit R (is just a setting in R studio)
search on windows for Set up ODBC data sources (32 bit)
Go to System DSN>Add
Choose Driver do Microsoft Access (*.mdb) > Finish
Data source name: ProjecnameAcc
Description: ProjectnameAcc
Make sure to actually select the database > OK

Now I could run the code that I liked

channel <- odbcConnect("ProjectnameAcc")
Table1Dat <- sqlFetch(channel, "Table1")

Solution 4:

I came across this SO when encountering a similar problem and at this point we have at least one more option with the extremely flexible odbc library.

An important note here though: the MS Access ODBC driver is not part of a default MS Office installation so you will have to download the appropriate driver from Microsoft (Microsoft Access Database Engine 2016 Redistributable in my case) and be sure to download the appropriate bitness (e.g. AccessDatabaseEngine_X64.exe). Once that has been downloaded it should automatically show up in your Windows ODBC Data Sources (64-bit) utility or you can confirm inside an R session with the odbcListDrivers function.

library(odbc)

# run if you want to see what drivers odbc has available
# odbcListDrivers()

# full file path to Access DB
file_path <- "~/some_access_file.accdb"

# pass MS Access file path to connection string
accdb_con <- dbConnect(drv = odbc(), .connection_string = paste0("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=",file_path,";"))