Programmatically read Access (.mdb) files into R for both Windows and Mac

Question

I am trying to write an open data package that reads New York State education data into R. That data are provided as an Access database.

I want to write a function that downloads, reads, and imports those files, and I want it to be supported across platforms. The existing approach suggests installing a 32-bit version of R, which does not fit the bill for programmatic access.

A tour of #RStats suggests that this is a common pain point (1, 2, 3, 4)

On OS X/Linux, you can use mdb.get from the Hmisc package, provided that you have a third party library called mdb-tools. But this seems to be of little help for Windows.

An accepted answer will read the .mdb file linked above into R across Windows, OS X, and Linux.

Answer 1

How about just with RODBC? Can you also download and use the mdb file (e.g. to make queries/views directly inside the mdb?)

I usually load data from Access dbs into R with the following code chunk:

# read in the data
library(RODBC)
db <- odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};
                        DBQ=C:\\Path\\To\\Database\\my_db.accdb")

# Get data
data <- as_tibble(sqlFetch (db , "Table or Query Name", rownames=TRUE))