I am trying to use some ms access databases in R (version 1.9.1 or 2.0 on a Debian system). In searching the net for promising software to do this, I found mdbtools. Mdbtools claims the ability to convert schemas and tables in MS Access to MySQL and other databases. http://mdbtools.sourceforge.net/ I'm wondering if anyone in the R community has tried using this software to use MS-Access databases in R with a Linux system. If so, Were you successful? What kind of problems did you encounter? Thank-you for your attention. Anne York
mdbtools claims to have an ODBC driver, so no conversion should be necessary, just use RODBC. Both last time I tried and just now I could not get it to compile. The problems were in its Gtk front end, but that appears not to be optional. On Mon, 1 Nov 2004, Anne York wrote:> I am trying to use some ms access databases in R (version > 1.9.1 or 2.0 on a Debian system). In searching the net for > promising software to do this, I found mdbtools. Mdbtools > claims the ability to convert schemas and tables in MS > Access to MySQL and other databases. > > http://mdbtools.sourceforge.net/ > > I'm wondering if anyone in the R community has tried using > this software to use MS-Access databases in R with a Linux > system. If so, Were you successful? What kind of problems > did you encounter?-- Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595
01.11 2004 r., on 19:14 Anne York wrote:> I am trying to use some ms access databases in R (version > 1.9.1 or 2.0 on a Debian system). In searching the net for > promising software to do this, I found mdbtools. Mdbtools > claims the ability to convert schemas and tables in MS > Access to MySQL and other databases. > > http://mdbtools.sourceforge.net/ > > I'm wondering if anyone in the R community has tried using > this software to use MS-Access databases in R with a Linux > system. If so, Were you successful? What kind of problems > did you encounter? >I tried mdbtool on my linux box and I can say that it supports MDB format quite well but I would like to say that there is no _good_ software which provides GUI for basic operations on MDB files like table management, querying etc. So, if you want to be sure that your data won't be damaged I'd not recommend you to use MDB files on Linux. Personally, I use Linux and I'm its funboy, so don't call me Microsoft's man ;-) but that is a fact. I have many databases in MDB files and I converted them to sqlite or mysql databases on Linux to be able to work with them on Linux. For sqlite (which I strongly recommend) there is no problem, because you can use ODBC drivers for sqlite and export data using MS Access. sqlite wors very well on both Linux & Windows, so you don't have to do any conversion. MDB to Mysql is a little more complex. I did it that way 1. install ODBC drivers for mysql on Windows machine 2. configure ODBC (DSN) connection to mysql database on Linux (I assume you have mysql on linux configured and you have ethernet network connecting both machines). 3. open Microsoft Access, open ODBC conn to mysql in access, 4. import access databas to that connected remotely from linux (mysql) Another way is to use freeware software called DBTools for Windows (search for dbtools by google, use first link). This software provides export/import functionality between mysql/postgresql/sqlite/access. Somtimes I use it and it works well. I believe it will help you. Greets -- Mateusz ??oskot mateusz at loskot dot net
Anne York <york at zipcon.net> writes:> I am trying to use some ms access databases in R (version 1.9.1 or 2.0 > on a Debian system). In searching the net for promising software to > do this, I found mdbtools. Mdbtools claims the ability to convert > schemas and tables in MS Access to MySQL and other databases. > > http://mdbtools.sourceforge.net/ > > I'm wondering if anyone in the R community has tried using this > software to use MS-Access databases in R with a Linux system. If so, > Were you successful? What kind of problems did you encounter?I have used it several times. The ODBC driver is not really (at all?) working but the command line tools seem to work well enough. I modified mdb-export and created a script that worked like mysqldump (to produce CREATE TABLE and INSERT statements) so that I was able to get the tables into MySQL to use with R. The latest version is quite a bit more recent than the one I have been using and I believe it has more features. Here is something that I have just tried and seems to work and avoids the need to take your data into another database: x <- read.table(pipe('mdb-export -d "\t" databasename.mdb tableName'), sep="\t", header=TRUE) mdb-export exports the contents of a table from an Access database (fields separated with commas by default). The -d option specifies the delimiter (I prefer to use a tab). This seems to work well on my relatively small test database. I guess it would not take much work to write a little set of functions to get the table names (using mdb-tables) and do some other useful things. Not as good as having a working ODBC driver, but quite nice all the same. I'll have spend a little more time playing with this... Dave -- David Whiting University of Newcastle upon Tyne, UK.