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

Sorayasorb

Also maybe this answer may be helpful as well, I'm not sure. – Indiscreet

Thank you Joran. I will try both options. – Sorayasorb

It worked wit 32-bit. Thanks. – Sorayasorb

This error is not caused by the Windows install, but if you have 32-bit Office installed and try to use 64-bit R. I've added a script below that will start a second 32-bit R session to read the data from 32-bit Access and then copy the data back to the original 64-bit R session. – Aery

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

Keever

Thank you mplourde. I just tried it and gives a line of errors:( – Sorayasorb

Yes, I am using 64 bit R. I will change it to 32 bit if it can help. – Sorayasorb

Yes, I now changed it to 32 bit R and it is working. Thanks for the tip. – Sorayasorb

I am trying this but still I am facing same issue. Its connecting to the database in amazon but not access – Hibernal

@NealFultz this depends on whether you have 32 or 64bit Office installed – Keever

It worked for me installing AccessDatabaseEngine_x64.exe – Liver

I found that I can't rely on my working directory and just put the filename. You have to put the full path. – Rob

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.

Aery

It's slightly safer to use prog <- file.path(R.home(), "bin", "i386", "Rscript.exe") avoiding relying on R_HOME. – Bilateral

This function is awesome, but I noticed that since R 4.2.0, 32-bit builds are no longer provided (cran.r-project.org/bin/windows/base/…) – Improvise

Answer 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

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

Uncivil

It's even simpler than described above: download the last 32-bit R which is version 4.1.3. Start R/bin/i386/Rgui.exe, install RODBC package, connect to .mdb file with con <- odbcConnectAccess2007("mydatabase.mdb"). Profit. – Beginning

Hi @Beginning if the only thing needed at the moment is the addition of 2007 that may indeed be easier. However I would say that is more of an answer to the question rather than a comment to my answer so I would recommend you to post the answer as a separate response so people may also vote or comment on it as they try! (Though do see the other answers to check if you add something) – Uncivil

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

Vagal

This did the trick for me on Windows 10. If it is not working, run odbcListDrivers(). If "Microsoft Access Driver (*.mdb, *.accdb)" doesn't appear, you will need to install the Access redistributable discussed above. When I tried to install AccessDatabaseEngine_X64.exe I got an error about a "Office 16 Click-to-Run Extensibility Component" conflict. I was able to resolve this conflict by following the directions on the error message. Once installed, the driver appeared when I ran odbcListDrivers() in R. – Module

I just had a version of this solution work for me. I was using R 4.2.1 64-bit, Windows 11 with 32-bit Microsoft Office. I had help from my IT department to install the 64bit Miscrosoft Access Driver as well as ODBC Driver 18 for SQL. After these drivers were installed, I was able to use the RODBC package as before without needing to do the setup for ODBC sources as described in the answer above (ie set up a custom DNS pathway) – Purposeless

Answer 5

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)

Bloodline

Answer 6

The function by manotheshark above is very useful, but I wanted to use an SQL query, rather than a table name, to access the database and also to pass the database name as a parameter since I commonly work with a number of Access databases. Here is a modified version:

access_sql_32 <- function(db_sql = NULL, table_out = NULL, db_path = NULL) {
  library(svSocket)

  # variables to make values uniform
  sock_port <- 8642L
  sock_con <- "sv_con"
  ODBC_con <- "a32_con"

  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("%1$s <- sqlQuery(%3$s, \"%2$s\")", table_out, db_sql, 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, table_out))
    expr <- c(expr, "odbcCloseAll()")
    expr <- c(expr, sprintf("close(%s)", sock_con))
    expr <- paste(expr, collapse=";")

    # launch 32 bit R session and run the expression we built
    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)
  }
}

I also had some difficulty working out how to call manotheshark's function and it took some delving into the svSocket package documentation to realise that the calling script needs to instantiate the object in which the data will be returned and then to pass its NAME (not the object itself) in the table_out parameter. Here is an example of an R-script that calls my modified version:

source("scripts/access_sql_32.R")
spnames <- data.frame()
# NB. use single quotes for any embedded strings in the SQL
sql <- "SELECT name as species FROM checklist 
        WHERE rank = 'species' ORDER BY name"
access_sql_32(sql, "spnames", "X:/path/path/mydata.accdb")

This works, but has limitations.

Firstly, avoid any Microsoft Access SQL extensions. For example, if you use the Access Query builder, it will often insert field names like [TABLE_NAME]![FIELD_NAME]. These will not work. Also Access allows non-standard field names that start with a digit like "10kmSq" and allows you to use them in SQL like SELECT [10kmSq] FROM .... This also won't work. If there is an error in the SQL syntax, the return variable will contain an error message.

Secondly, the amount of data you can return appears to be limited to 64Kb. If you try to run SQL that returns too much, the 32-bit session does not terminate and the script hangs.

Iata

Thanks a lot, that nailed it! – Pecuniary

Answer 7

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

Yockey

Answer 8

I am running Windows 10 x64, Office 365 x64 (not sure that is relevant), and R 64-bit. I did not need to switch to 32-bit R.

In my case, I got it working by installing the 64-bit version of the Microsoft Access Database Engine 2016 Redistributable and then by giving my own account, which rsession.exe runs as, Full Control permissions on the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC registry key.

The permissions on registry key make no sense. My account is already a member of this PC's Administrators group, and that group already has Full Control permission on that key.

The commands I used:

library("odbc")

accdb_con <- dbConnect(drv = odbc(), .connection_string = paste0("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:/full_path_to_file/buildings.mdb;"))

Shanitashank