Access 2013 database read in R through RODBC

Question

I am trying to open an access database in R using RODBC but I've been unable to do so.

Based on this post, I've converted the R version being used in Rstudio to 32 bit instead of 64 bit.

I have also tried using the language in this post:

odbcDriverConnect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ=<fileSpec>')

I have also looked over possible reasons in this post as to why the connection is not being established.

The files are not locked and I have changed my R version to a 32 bit to match the ODBC drivers 32 bit. I am also able to establish odbc connection to the access driver using STATA, so I don't think I have any permission problems.

When listing the available data sources, I get the following showing the driver I'm trying to access:

> odbcDataSources()
                                             Excel Files 
"Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)" 
                                      MS Access Database 
              "Microsoft Access Driver (*.mdb, *.accdb)"

When trying to just connect to the Access Data Source, I get the following error:

ch <- odbcConnect("MS Access Database")
Warning messages:
1: In RODBC::odbcDriverConnect("DSN=MS Access Database") :
  [RODBC] ERROR: Could not SQLDriverConnect
2: In RODBC::odbcDriverConnect("DSN=MS Access Database") :
  ODBC connection failed

Parfait · Accepted Answer · 2018-04-03T20:28:34.370

MS Access is a file-level DBMS. Hence, you need to specify the location of your database. The default MS Access Database DSN alone will not suffice. Without specifying the file, your error is reproduced.

conn <- odbcConnect("MS Access Database;DBQ=C:\\Path\\To\\Database.accdb")
df <- sqlQuery(conn, "SELECT * FROM myTable")
odbcClose(conn)

Now if you did create your own user-defined DSN with the .mdb/.accdb file specified, you certainly can connect with it alone.

conn <- odbcConnect("myDSN")
df <- sqlQuery(conn, "SELECT * FROM myTable")
odbcClose(conn)

score 0 · Answer 2 · answered Apr 04 '18 at 03:27

This should do what you want.

library(RODBC)

# for 32 bit windows
# Connect to Access db
# channel <- odbcConnectAccess("C:/Users/Excel/Desktop/Coding/Microsoft Access/Northwind.mdb")

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


# for 64 bit windows
channel <- odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:/Users/Excel/Desktop/Coding/Microsoft Access/Northwind.mdb")

data <- sqlQuery( channel , paste ("select * from CUSTOMERS"))

odbcCloseAll()

Access 2013 database read in R through RODBC

2 Answers2