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.
In R, there are two main ways to connect with Access databases: using the ODBC (Open DataBase Connectivity) facility available on many computers; and using the DBI (DataBase Interface) package in R. These notes deal with ODBC only.
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.
The free MDB Viewer Plus program can also open and edit ACCDB files. This is a great alternative if you don't have a copy of Access. Plus, it's totally portable, so you don't even need to install it. Another way to open and edit one of these files without Access is to use OpenOffice Base or LibreOffice Base.
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
The title of the page you linked, RODBC: ODBC Database Access, may be misleading. Access doesn't mean MS Access; in that title access means connectivity. RODBC is an ODBC manager for R. It serves as the mediator to provide communication between R and the ODBC driver for your target database. So for GNU/Linux, you would still need an ODBC driver for MS Access database files ... RODBC doesn't provide one.
However, I don't know of any free (as in freedom and/or beer) MS Access ODBC drivers for Linux. Easysoft sells one, but it's not cheap. There may be offerings from other vendors, too; I haven't looked.
It might be easier to use a Windows machine to export your ACCDB to a format R can use. Or run R on Windows instead of Linux.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With