How to connect R with Access database in 64-bit Window?
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")
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.