[R] A little more on R, mdbtools and Access databases

[David Whiting]

Anne York <york at zipcon.net> writes:

> Wow, thanks for all the good information and ideas on this topic.
> 
> Have you used mdbtools to convert a data base to something like MySQL
> or other open source data base?  

Yes, that's mostly how I have used mdbtools so far.  I modified
mdb-export and created a script to use my modified mdb-export and
mdb-schema to act like MySQL's mysqldump, i.e. to produce CREATE TABLE
and INSERT statements. That way I was able to get tables straight from
an Access database into MySQL. Here's a link to what I did:

http://sourceforge.net/tracker/?group_id=2294&atid=302294&func=detail&aid=857342

With those changes and the mdbdump script I am able to do the
following to get a table into MySQL:

mdbdump accessDB accessTable | mysql mysqlDB


As it is now mdb-export produces output in delimited format so you
could use mdb-schema to create the table, export the data using
mdb-export and then load it into mysql (or whatever).

[it looks like the INSERT statements option has been added to
mdb-export, but I am not sure about the MySQL backend]

> It seems that if I needed to do
> unions or intersections, I would have to do the conversion and use the
> other database from R since I'd rather work on my Linux machine.

Another alternative would be to use mdbtools with the R functions I
posted earlier to read the tables into R and then use RODBC to save
(sqlSave) them into another database.  This would probably be the
easiest because all you need to do is get the current mdb-tools as it
is now (my changes were made to an old version).  This is now the my
preferred route. BTW, I have now tidied the functions up a little so
they don't create temporary files anymore.

> 
> On the negative side, Brian Ripley reported problems compiling
> mdbtools, and on the Debian website, there were some security alerts
> (overflow problems) about mdbtools. Clearly, you were able to compile
> mdbtools. 

I had to make some symlinks to get it all to play properly (it seemed
to expect libraries in /usr/lib when they were in /usr/local/lib), but
it seemed to compile okay.  The lib problem might have been a problem
on my side though.  

> Do you know anything about the security risks?

Not a sausage. I use on my local machine so I don't think that they
apply to my situation. I could easily be wrong though...

Dave


-- 
David Whiting
University of Newcastle upon Tyne, UK