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 ______________________________________________ [EMAIL PROTECTED] mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
