Importing Microsoft Access Files (.accdb/.mdb) in R

Let’s start with saying that importing MS Access files into R can be easy, but it isn’t necessarily so. There’s some compatibility issues that you need to know about. This blog post elaborates on these errors and how I solved them.

Import tables from an .mdb file

Opening a connection to your .mdb access database file and loading in a complete table is fairly easy. A widely used library to perform this task is RODBC, which enables Open Database Connectivity (ODBC) in R.

library(RODBC)

con <- odbcConnectAccess('your_db_file.mdb')
sqlFetch(con, 'your_table')

odbcClose(con)

If you have a 32-bit version of MS Office installed on your computer and you use the 32-bit version of R and your Access file has the .mdb extension, this will probably have worked flawlessly. However, that’s quite a lot of requirements.

If you use 64-bit R, you will run into the following error:

Error in odbcConnectAccess(“your_file.mdb”) : odbcConnectAccess is only usable with 32-bit Windows

If your tables aren’t huge (+4GB), you can simply change R to the 32-bit version. In RStudio, this is done via Tools > Global Options. You will have to restart RStudio.

Import tables from an .accdb file

To import .accdb files, the same requirement holds: you need to be working in the 32-bit version of R. The function to create the connection is very similar as with .mdb files.

library(RODBC)

con <- odbcConnectAccess2007('your_db_file.accdb')
sqlFetch(con, 'your_table')

odbcClose(con)

However, it is very likely that you’ll run into this error:

1: In odbcDriverConnect(con, …) :  [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified2: In odbcDriverConnect(con, …) : ODBC connection failed

That’s because you need the 32-bit MS Access 2007 drivers. Luckily, you don’t need to install another version of MS Office. The drivers have been made available in a stand-alone installer, that you can get here. If you install this file, you’ll be able to access .accdb files in R. You don’t even need to restart after installing these drivers.

By the way, if you’re having trouble understanding some of the code and concepts, I can highly recommend “An Introduction to Statistical Learning: with Applications in R”, which is the must-have data science bible. If you simply need an introduction into R, and less into the Data Science part, I can absolutely recommend this book by Richard Cotton. Hope it helps!

Great success!