Hi again, I have played a little more with mdbtools and R. I downloaded the latest version of mdbtools from sourceforge (version 0.6pre1). Quickly scanning the mailing list suggests that ODBC seems to work with PHP but I have not been able to get it to work with R. I can make a connection to the database and when I do a query I get back the names of the rows but not the data. I must admit I have not spent long trying to figure it out. For my own use I am able to work directly with a database file on my local machine, and to make things easier for myself I have concentrated on making some simple functions (based on those I posted yesterday) that use mdbtools to: i) get table names, ii) describe tables, iii) read tables into R, and iv) use the (basic) SQL functionality of mdbtools to perform simple queries (really only able to select subsets of columns and rows) Here is an example session using a database I found on the web at http://www.microsoft-accesssolutions.co.uk/downloads/login.zip> db <- "/home/dave/tmp/login.mdb" > mdbTables(db)[1] "MSysObjects" "MSysACEs" "MSysQueries" [4] "MSysRelationships" "MSysAccessObjects" "tblEmployees"> mytab <- mdbTables(db)[6] > mytab[1] "tblEmployees"> x <- mdbReadTable(db, mytab) > str(x)`data.frame': 4 obs. of 4 variables: $ lngEmpID : int 1 2 3 4 $ strEmpName : Factor w/ 4 levels "David","Gavin",..: 3 2 4 1 $ strEmpPassword: Factor w/ 4 levels "david","gavin",..: 3 2 4 1 $ strAccess : Factor w/ 2 levels "Admin","User": 1 2 2 2> head(x)lngEmpID strEmpName strEmpPassword strAccess 1 1 Graham graham Admin 2 2 Gavin gavin User 3 3 Lynne lynne User 4 4 David david User> mdbDescribe(db, mytab)ColumnName Type Size 1 lngEmpID Long Integer 4 2 strEmpName Text 20 3 strEmpPassword Text 20 4 strAccess Text 40> mdbQuery(db, "select lngEmpID, strAccess FROM tblEmployees where lngEmpID < 3")lngEmpID strAccess 1 1 Admin 2 2 User>Here are the functions: ### Some quick code to make use of mdb-tools to use MS Access tables in R. ### 2004-11-02 ### David Whiting require(gdata) # for the trim function. mdbTables <- function(dbname) { system(paste("mdb-tables -d '\t' -S", dbname), intern=TRUE) } mdbReadTable <- function(dbname,tableName) { tableName <- dQuote(tableName) read.table(pipe(paste("mdb-export -d '\t' ", dbname, tableName)), sep="\t", header=TRUE) } mdbDescribe <- function(dbname,tableName) { tableName <- dQuote(tableName) cat("describe table ", tableName, "\ngo", file = "tempR.sql") mdesc <- system(paste("mdb-sql -i tempR.sql ", dbname), intern=TRUE) mdesc <- strsplit(substring(mdesc[-c(1:3,5, length(mdesc), length(mdesc)-1)], 2), "\\|") tabDesc <- rbind(mdesc[[2]]) for (i in 3:length(mdesc)) { tabDesc <- rbind(tabDesc, mdesc[[i]]) } tabDesc <- matrix(trim(tabDesc), ncol=3) tabDesc <- data.frame(tabDesc) names(tabDesc) <- c("ColumnName", "Type", "Size") tabDesc$Size <- as.numeric(levels(tabDesc$Size)[tabDesc$Size]) system("rm -f tempR.sql") tabDesc } mdbQuery <- function(dbname, mstatement, header=FALSE, footer=FALSE) { cat(mstatement, "\ngo", file = "tempR.sql") sqlOptions <- "-p" if (!header) sqlOptions <- paste(sqlOptions, "H", sep="") if (!footer) sqlOptions <- paste(sqlOptions, "F", sep="") sqlStatement <- paste("mdb-sql", sqlOptions) tmp <- read.table(pipe(paste(sqlStatement, "-i tempR.sql", dbname)), sep="\t") names(tmp) <- trim(unlist(strsplit(substr(mstatement, 7, regexpr(" [Ff][Rr][Oo][Mm]", mstatement)[1]), ","))) system("rm -f tempR.sql") tmp } -- David Whiting University of Newcastle upon Tyne, UK
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
Prof Brian Ripley
2004-Nov-04 07:21 UTC
[R] A little more on R, mdbtools and Access databases
On 3 Nov 2004, David Whiting wrote:> Anne York <york at zipcon.net> writes:> > 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.I was able to compile mdbtools 0.6pre1 (which you will have trouble finding from their website, so search for the project on sourceforge) without any problems. The public 0.5 gave me so many problems that I gave up on a 64-bit platform, and gave up on their ODBC module on a 32-bit one. However, although the ODBC module makes communications, it reports its info incorrectly and reports syntax errors with even the simplest SQL, so it is clearly not ready for use. -- 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