[R] Reading MS Access Database Files?

[Jason Turner]

Hi

> Does anyone know of a way to convert or read MS Access database files under Linux
> so I can access them in R? I had contacted  Concdeptual Software about DBMS/Copy
> and they assured me that the Linux version could handle Access files. Of course,
> when I purchased it then they said that it couldn't read MS Access files.
> 
> Rick Bilonick


Haven't tried exactly this, so take it with the appropriate scoops of salt...

It may be possible to export the data as an ODBC source, and use RODBC
to read it.  The more I think about it, though, the more hurdles present
themselves.  The biggest problem is the fact that Access behaves as a 
self-contained database package - I don't know of any way to make it behave
like a database server, providing data to other applications on demand.

The RODBC package is something I've used rather a lot under R, but never
to read an Access database.  I usually export the Access data via ODBC
to a network-aware database server (usually PostgreSQL), and then
use RODBC or RPgSQL from there.  

Once the data is transferred to the server, I also usually use that as 
my workbase - removing or renaming the original tables within Access
and then linking the tables via ODBC from the database server back
to Access, so version control and concurency aren't a problem.

[somewhat off-topic - perl as a tool for the job]

One possibility (if you're comfortable with perl) is the DBI package, 
using the DBD::Proxy functionality, and "roll your own" server connection.
This is less elaborate than it sounds - the infrastructure to do this
is already provided within DBI and DBD::Proxy, including finnicky details
like compression for transmission over slow networks, and encrypted
transmission.

R would use system() to call a perl script on the linux machine,
which would connect to a (running in background) perl script on the 
windows machine.  The perl script on the Windows machine would excecute
the data fetching, and pass it back to the perl script on the linux 
machine.  From there, the values could be passed back to R, or to
an external file.  

This sounds pretty involved, but perl isn't hard to learn if you
don't know it, and if you do, the DBI module isn't difficult.

Hope this helps - I've never tried it exactly like this.  Let me know
what you find.

Cheers

Jason
-- 
Indigo Industrial Controls Ltd.
64-21-343-545
jasont at indigoindustrial.co.nz
-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._