Connecting to MS Access Database from R (x64)

Question

I am trying to access my Access database using R. I have tried the below options but I am not able to connect to the database. I am using 64 bit R and 64 bit Access. I am using windows 7 also.. :(

library(RODBC)
db <- "E:/testdb.accdb"
myconn <-odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=E:/test.accdb")
con2 <- odbcConnect(db)
channel <- odbcConnectAccess("E:/testdb")

I always get the error. Please help me.

1: In odbcDriverConnect("DSN=E:/testdb.accdb") :
  [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect("DSN=E:/testdb.accdb") : ODBC connection failed

Spire

Could you add some system information? What operating system are you using, what version of Access and R (32 bit vs 64 bit for each)? In my experience, there are tricky issues with ODBC connections in win7. – Threshold

If you do odbcDataSources() in R, do you see the connection to your database? If not you will need to add it using the ODBC data sources tool in Windows. – Threshold

I've come across this problem, it has been a super frustrating. I'm working with win7 64 and 32 bit access. I switched to 32 bit R and after lots of fidling around I finally got my DB to work using the following. db<-file.path("yourDB.accdb") channel<-odbcConnectAccess2007(db). – Property

Answer 1

I think the problem is as error message suggests - you don't have x64 Microsoft Access driver installed. Check it in Control panel - ODBC Data Sources (x64) on the Drivers tab. If it's not there, try to install Microsoft Access Database Engine 2010 Redistributable.

Anamorphic