3

I'm trying to connect R with an MS Access database on a Windows 7 64bit machine. 

library(RODBC)
con <- odbcConnectAccess2007("M:/path/to/mydatabase/my.database.accdb")
# [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specifiedODBC connection failed

I've also tried package odbc using connection strings specified at https://www.connectionstrings.com/access/ but similarly cannot connect

library(DBI)
con <- dbConnect(drv = odbc::odbc(), dsn = "Microsoft Access Driver", driver = "Microsoft Access Driver (*.mdb, *.accdb)", database = "M:/path/to/mydatabase/my.database.accdb")
# Error: nanodbc/nanodbc.cpp:950: IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

What am I doing wrong?

mloskot
  • 37,086
  • 11
  • 109
  • 136
mark
  • 537
  • 6
  • 25
  • You can use my [AceOdbcCheck](https://github.com/gordthompson/AceOdbcCheck) script to test your machine for a valid install of Access "ACE" ODBC. If you don't have it installed you can download the drivers [here](http://www.microsoft.com/en-US/download/details.aspx?id=13255). Note that you need the "bitness" (64-bit or 32-bit) that matches the version of R you are running. That is, if you are running 32-bit R then you need the 32-bit drivers even though you are running 64-bit Windows. – Gord Thompson Jul 21 '17 at 14:44
  • Thanks @Gord Thompson. That was the problem. Post as an answer and I'll accept it. Nice work. For anyone that's interested the correct string for connection using `library(DBI)` is `contest <- dbConnect(drv = odbc::odbc(), .connection_string = "Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=M:/path/to/mydatabase/my.database.accdb")` – mark Jul 21 '17 at 15:06

1 Answers1

3

You can use my AceOdbcCheck script to test your machine for a valid install of Access "ACE" ODBC. If you don't have it installed you can download the drivers here.

Note that the "bitness" (64-bit or 32-bit) of the Access ODBC driver must match the version of R you are running. That is, if you are running 32-bit R then you need the 32-bit drivers even though you are running 64-bit Windows.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418