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