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

Question

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

Answer 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") 

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