A little more on R, mdbtools and Access databases

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