Search code examples
rms-accessodbcr-dbinanodbc

Connect MS Access database to R

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?

Solution

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