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.

Ecumenicist

Um…did you look at the contents of the ZIP file? the .mdb file is a single-table with what looks like the exact same data as the plain text and highly cross-platform .tab file. – Housewifely

Hi @Housewifely - I probably should have picked a different example in the link - the goal is to read many years of data, and NY State only started releasing the .tab in the past year. If you look at data.nysed.gov/files/assessment/13-14/3-8-2013-14.zip for instance, you'll see that it's only a .mdb. – Ecumenicist

If you look at the twitter links you'll see that @david-lawrence-miller, author of the R Distance package has also bumped into this problem. Not sure if the close vote is yours but if you could reconsider I would be grateful. – Ecumenicist

if is your friend. there's nothing wrong with wrapping platform specific solutions (i.e. RODBC for Windows and mdb.get() for linux or macOS) in if statements. Sometimes you actually have to work to get the data in. – Housewifely

@Housewifely Good call. Andrew you can answer your own question via an if/else. – Paphlagonia

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

Breakdown

This doesn't answer the question because the OP is already aware of the RODBC approach (link in 2nd paragraph), found it didn't work for their problem because of the requirement to use 32-bit R, and asked for an alternative solution that will work across platforms. – Unbound

Thanks @Breakdown - I will give that a try! – Ecumenicist