How to connect R with Access database in 64-bit Wi

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

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

Answer 2

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

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

Now I could run the code that I liked

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

Answer 3

Using advice from others, here's an explicit example of getting 32-bit Access data into 64-bit R that you can write into a script so that you don't need to do the steps manually. You do need to have 32-bit R available on your machine for this to run, and this script assumes a default location for the 32 bit R, so adjust as needed.

The first code part goes into your main script, the second code part is the entire contents of a little R script file that you create and is called from the main script, this combination extracts and saves and then loads the data from the access database without having to stop.

Here's the bit that goes in my main script, this is run from within 64 bit R

##  Lots of script above here
## set the 32-bit script location
pathIn32BitRScript <- "C:/R_Code/GetAccessDbTables.R"
## run the 32 bit script
system(paste0(Sys.getenv("R_HOME"), "/bin/i386/Rscript.exe ",pathIn32BitRScript))
## Set the path for loading the rda files created from the little script 
pathOutUpAccdb <- "C/R_Work/"
## load the tables just created from that script
load(paste0(pathOutUpAccdb,"pots.rda"))
load(paste0(pathOutUpAccdb,"pans.rda"))
## Lots of script below here

Here's the bit that is the separate script called GetAccessTables.R

library(RODBC).    
## set the database path
inCopyDbPath <- "C:/Projects/MyDatabase.accdb"
## connect to the database
conAccdb <- odbcConnectAccess2007(inCopyDbPath) 

## Fetch the tables from the database. Modify the as-is and string settings as desired
pots <- sqlFetch (conAccdb,"tbl_Pots",as.is=FALSE, stringsAsFactors = FALSE)
pans <- sqlFetch(conAccdb,"tbl_Pans",as.is=FALSE, stringsAsFactors = FALSE)
## Save the tables
save(pots, file = "C/R_Work/pots.rda")
save(pans, file = "C:/R_Work/pans.rda")
close(conAccdb)

Answer 4

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 5

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.

Answer 6

The following solution worked for me: found on reading-data-from-32-bit-access-db-using-64-bit-R It says to install the 64-bit database engine from: microsoft`

Then: find and run "ODBC-datasources (64-bits)".

1. In the tab "user-DSN" click "Add"
2. Select: "Microsoft Access Driver" and save
3. Give your new datasource a name (you will use this name when connecting to the database later)
4. Click "Select": Select the directory where your access database(s) exist and save

Then in R:

library(RODBC)
dcon <- dbConnect(odbc::odbc(), "name-you-gave-to-your-datasource-in-3")