Reading a access database (mdb) in 64 bit in R

Question

I have a database and I need to read that in R. I found some packages such as Hmisc and RODBC which have the functions to do that. I am using windows and was not able to use Hmisc because you need to have mdb-tools package and I found no tutorial or way to install mdb-tools on windows.

Now, I was trying to start with RODBC. I found this question "How to connect R with Access database in 64-bit Window?" which shows how to have a connection in windows. I tried to use the command similar to what was accepted answer in that question.

odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=E:/Projects\Newdata/2013 Database/Data/pgdabc_SW.mdb")

It gives the following error :

1: In odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)}, DBQ=E:/Projects\Newdata/2013 Database/Data/pgdabc_SW.mdb") :
  [RODBC] ERROR: state 01S00, code 0, message [Microsoft][ODBC Driver Manager] Invalid connection string attribute
2: In odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)}, DBQ=E:/Projects\Newdata/2013 Database/Data/pgdabc_SW.mdb") :
  ODBC connection failed

I am not sure how to check and start diagnosing what's going on here. I went to administrative tools and checked the options on "Data Sources (ODBC)". . I changed the target to sysWOW.

Then I created a new data source as follows:

I am not sure if I need to select database or not. I found Brian Ripley's http://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf RODBC tutorial but still I am not able to make it work.

Answer 1

This works fine for me & might work for you, too:

require(RODBC)
conn <- odbcConnectAccess2007(path.expand("~/Database.accdb")) 
subset(sqlTables(conn), TABLE_TYPE == "TABLE") 
df <- sqlFetch(conn, "Table1") 
close(conn) 

My sessionInfo():

# R version 3.1.1 (2014-07-10)
# Platform: x86_64-w64-mingw32/x64 (64-bit)
# 
# other attached packages:
#   [1] RODBC_1.3-10
# 
# loaded via a namespace (and not attached):
#   [1] tools_3.1.1

Answer 2

I have had issues with this (trying to query Access 32bit from R 64bit) from a long time. I think it has been fixed in windows 10. I made a kludge by modifying something I found in this post: How to connect R with Access database in 64-bit Window?

I made a function that saves a script (which in turn connects to the database and saves the result of the query), run it using R32, and load the data into the R64 work environment. I prepared it for Access 2007, but something analogue could be done for Access2003 (just using odbcConnectAccess instead of odbcConnectAccess2007) or other 32 bit databases

MysqlQueryAccess2007<-function(filename,query){
tempdir=gsub('\\\\','/',tempdir())
txt<-paste("if (!'RODBC' %in% installed.packages()) install.packages('RODBC')
         require(RODBC)

         channel<-odbcConnectAccess2007('",filename,"')
         data<-sqlQuery(channel,\"",query,"\")
         save(data,file=paste('",tempdir,"','tempRODBCquery.Rdata',sep='/'))
         close(channel)",sep="")

  writeLines(txt,con=paste(tempdir,'RODBCscripttemp.r',sep='/')->tempscript)
  system(paste0(Sys.getenv("R_HOME"), "/bin/i386/Rscript.exe ",tempscript))
  tt<-get(load(paste(tempdir,'tempRODBCquery.Rdata',sep='/')))
  return(tt)
}

Then you only have to do the queries this way:

dat<-MysqlQueryAccess2007("samplefile.accdb","SELECT TOP 5 * FROM TableI")

Answer 3

Have been trying to figure it out for a while myself. Solution given in the accepted answer here Reading data from 32-bit Access db using 64-bit R, credits to @erg, as well as here How to connect R with Access database in 64-bit Window?, credits to @JATT.

The bottom line:

1. Install 64-bit Microsoft Access drivers https://www.microsoft.com/en-us/download/details.aspx?id=54920
2. Setup appropriate System DSN in ODBC Data Sources (64-bit)
3. In R 64-bit read .mdb file by using odbc package: dbConnect(odbc(), 'your_64bit_dsn').