Connect MS Access database to R

Question

I want to connect MS Access database to R with DBI package. I try this:

library(DBI)
 con <- dbConnect(odbc::odbc(), "BASE_MEPSA")

and I have this error

Error: nanodbc/nanodbc.cpp:950: HY024: [Microsoft][Pilote ODBC Microsoft Access] « (Inconnu) »

But with RODBC I have no problem

library(RODBC)
base1 <- odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:/Users/IPS/Desktop/divers/dt/stateduc_R/BASE_MEPSA.mdb")

How can I connect MS Access to R with the DBI package?

Answer 1

Is "BASE_MEPSA" the name of the Data Source Name (DSN)? You would have created this in your ODBC Data Source Administrator. If so, I can always successfully connect with:

library(DBI) 
cn <- dbConnect(odbc::odbc(), dsn="BASE_MEPSA")

Additionally, you need to make sure that you are using the same architecture. This means that you should be using the x32 versions of R, Access, and ODBC Data Source Admin if your Access version is x32. You have to change this in the global settings for R as the default is x64.

Also try using the file path you used for RODBC.

cn <- dbConnect(odbc::odbc(), DBQ="C:/Users/IPS/Desktop/divers/dt/stateduc_R/BASE_MEPSA.mdb")

Answer 2

library(RODBC) 
mdbConnect<-odbcConnectAccess("D:/SampleDB1/sampleDB1.mdb")


# Load RODBC package
library(RODBC)

# Connect to Access db
# for 32-bit windows
channel <- odbcConnectAccess("C:/your_path/Database1.accdb")

# Get data
data <- sqlQuery(channel , paste ("select * from Table1"))



#load package
library("RODBC") 

# for 64-bit windows
#connect database.
db<-file.path("C:/Users/Excel/Desktop/Coding/Microsoft Access/Split_and_Transpose.accdb") 

#internal RODBC function
channel<-odbcConnectAccess2007(db) 

#read particular table from Access database file.
dataSetName<-sqlFetch(channel,"Table1") 

#do not forget this, otherwise you lock access database from editing.
close(channel) 

This is a good resource.

https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf