0

I am trying to read MS Access db file .mdb into R

library(RODBC)

db <- odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};
                        DBQ=Input/some_db.mdb")

Here is my error:

[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specifiedODBC connection failed

I am not sure how to start troubleshooting.

user1700890
  • 7,144
  • 18
  • 87
  • 183
  • what does `file.exists("Input/some_db.mdb")` return? – MrFlick Jul 31 '20 at 22:09
  • @MrFlick, it returns `TRUE` – user1700890 Jul 31 '20 at 22:12
  • 2
    What does `odbcListDrivers()` return? – MrFlick Jul 31 '20 at 22:14
  • 3
    Not sure if it's related, but ... if the bit-ness of R does not agree with the bit-ness of the ODBC driver and of Office as installed on the system, I've had (debilitating) problems. (E.g., R-4.0.2-64bit won't be able to use Access ODBC drivers if you have Office 32-bit installed.) A quick test is to run the other bit-ness of R. – r2evans Jul 31 '20 at 22:16
  • @MrFlick, well it returns `Error in odbcListDrivers() : could not find function "odbcListDrivers"`. I guess I need to install `odbc` library first. I tried today but failed. I need to start separate topic for it. – user1700890 Jul 31 '20 at 22:16
  • 2
    You shouldn't need both `odbc` and `RODBC` to do basic operations, for the record. Does `odbcDataSources()` include anything? (I'm not an `RODBC` user, so I'm just guessing.) – r2evans Jul 31 '20 at 22:17
  • 2
    Try the older 32-bit version: `Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\Full Path\\To\\myDatabase.mdb` – Parfait Jul 31 '20 at 23:59
  • @Parfait, same error, I probably need to try it with 32-bit R-studio. Just tried 32-bit R-studio, it did not help. – user1700890 Aug 01 '20 at 01:10
  • 1
    What did [`odbcDataSources()`](https://rdrr.io/cran/RODBC/man/odbcDataSources.html) show? Note: this is different than function you tried above (from different package). This shows all available ODBC drivers on machine. See if MS Access drivers show. – Parfait Aug 01 '20 at 03:57
  • @Parfait, you are correct MS Access driver is not present, I need to try to install it. Do you think the following will solve the problem: https://www.microsoft.com/en-us/download/details.aspx?id=13255 – user1700890 Aug 01 '20 at 11:17

0 Answers0