How to read data from Microsoft Access .accdb database files into R?

Egon Willighagen picture Egon Willighagen · Aug 18, 2011 · Viewed 33.2k times · Source

The RODBC documentation suggests it is possible, but I am not sure how to read data from a Microsoft Access (the new .accdb format) file with this package into R (on Debian GNU/Linux). The vignette talks about drivers, but I do not quite understand how I can see which drivers are installed, and in particular, if I have a driver installed for me to access those .accdb files.

What code do you use to read data from .accdb files? And please indicate what platform you are on and if you had to install a special driver.

Answer

coip picture coip · May 11, 2016

To import a post-2007 Microsoft Access file (.accdb) into R, you can use the RODBC package.

For an .accdb file called "foo.accdb" with the following tables, "bar" and "bin", stored on the desktop of John Doe's computer:

library(RODBC)    #loads the RODBC package
dta <- odbcConnectAccess2007("C:/Users/JohnDoe/Desktop/foo.accdb")   #specifies the file path
df1 <- sqlFetch(dta, "bar")   #loads the table called 'bar' in the original Access file
df2 <- sqlFetch(dta, "bin")   #loads the table called 'bin' in the original Access file