1

I am working on a program that pulls data out of .mdb and .accdb files and creates the appropriate tables in R.

My working program on my Mac looks like this:

library(Hmisc)
p <- '/Users/Josh/Desktop/Directory/'
mdbfilename <- 'x.mdb'
mdbconcat <- paste(p, mdbfilename, sep = "")
mdb <- mdb.get(mdbconcat)
mdbnames <- data.frame(mdb.get(mdbconcat, tables = TRUE))
list2env(mdb, .GlobalEnv)
accdbfilename <- 'y.accdb'
accdbconcat <- paste(p, accdbfilename, sep = '')
accdb <- mdb.get(accdbconcat)
accdbnames <- data.frame(mdb.get(accdbconcat, tables = TRUE))
list2env(accdb, .GlobalEnv)

This works fine on my Mac, but on the PC I'm developing this for, I get this error message:

Error in system(paste("mdb-tables -1", file), intern = TRUE) : 
  'mdb-tables' not found

I've thought a lot about using RODBC, but this program allows me to have the tables arranged in a way where subsequent querying and dplyr functions work. Is there any way to get these function to work on a Windows machine?

Joshua Taylor
  • 27
  • 2
  • I am no expert, but: 1) which line causes the error? I'm guessing one of those where `mdb.get` is called, but you tell me. 2) this is a system call (see `?system` for what it does) which is known to behave differently on different platforms. Maybe herein lies the problem? – jakub Sep 07 '16 at 18:58
  • My bad. It's the `mdb.get` lines. Are you asking for the OS or the version of R? – Joshua Taylor Sep 07 '16 at 19:14
  • `system` is an R function that allows to you pass commands to your OS. This works well on UNIX-like systems (linux, Mac) but Windows works differently. You cannot use pipes in Windows, it does not have the shell we know and love etc. So, if `mdb.get` relies on a `system` call, it may not work properly on all platforms. Quick search reveals that sticking with only mdb.get will cause you pain... http://stackoverflow.com/questions/37912560/programmatically-read-access-mdb-files-into-r-for-both-windows-and-mac – jakub Sep 07 '16 at 19:21
  • That's not good... Do you think there's a way to access .mdb and .accdb tables without setting up a DSN, or do you think that's what the system call gets around? – Joshua Taylor Sep 07 '16 at 20:00
  • Honestly I'm not an expert :( if RODBC is known to work, I would use that, even if it means some extra effort to get the data to the right format. – jakub Sep 08 '16 at 08:29
  • I'm using `RODBC` now. The main issue is the manual creation of individual DSN's for each database, which is a tough problem since I have to deal with a bunch of databases. Not to mention that pulling out all the tables in a database at once is is much trickier in `RODBC`. Overall, a much less usable and effective package than `Hmisc`. – Joshua Taylor Sep 08 '16 at 13:34

0 Answers0