Having trouble importing your Microsoft Access Databases in R? Yeah, I had trouble <i>accessing</i> Access in R as well. After pouring 7/10’s of my soul into a project, it looked like I might have to analyze a dataset (over 100 Access Databases) in Excel, or worse, by hand. Luckily R, as always, came to the rescue. The trick that you’ll need is a library called “RODBC” — it’s a package that will allow Access’s notoriously stubborn ports to open wide for R to come in and grab information.
Below is a sample code for what I’ve been using. Not all of my conventions are the best, and not all of them will work for you, so I’ll explain what some of these more specific variables do.
NOTE: if you use 32-bit Microsoft Office, you will need to use the 32-bit version of R. I know that seems stupid, but those are the rules!
###Begin Script
###This particular build is for one of our lab's projects, but you can edit however you like.
###This assumes that you store your results as "SubjectName001, SubjectName002, OtherSubject001, OtherSubject002".
###Before running the loop, you will need to change how many sessions you want to run (HowManySessions), when the loop starts (StartSession),
###and the pigeon's name. Make sure you are using the directory that all of your file RESULTS are stored in. No need for sessions!
library(RODBC)
Directory<-"C:\\Users\\Alexander\\Documents\\Projects\\Stimulus Movement\\Results\\"
FileExtension<-".accdb"
HowManySessions <- 30
PigeonName <- "Raphael"
StartSession <- 4501 ###Highlight from here down, and press CTRL+R to start the loop!
for (i in 1:HowManySessions){
SessionString<-paste(PigeonName,StartSession, sep="")
db <- paste(Directory,SessionString,FileExtension, sep="")
connection <- odbcConnectAccess2007(db)
sqlTables(connection, tableType = "TABLE")$TABLE_NAME trial <- sqlFetch(connection, "TrialResults")
###Here's an example of pulling subsets of data out of the database.
###Calculating accuracy between these two groups of information, and then attaching it to a data frame called "numbers" below.
movetrial = trial[trial$NoTouch==1,]
stattrial = trial[trial$NoTouch==0,]
moveACC = 1 - mean(movetrial$IncorrectCorrections)
statACC = 1 - mean(stattrial$IncorrectCorrections)
numbers = data.frame(PigeonName, StartSession, moveACC, statACC)
###Now, we export it to a comma separated spreadsheet. Note that column names are turned off in this example!
write.table(numbers,
file='C:\\Users\\Alexander\\Documents\\Projects\\Stimulus Movement\\Results\\omnibus.csv', row.names=F, col.names=F, append=T, sep=",")
close(connection)
StartSession <- StartSession + 1
}
###You can use this command to close all ODBC connections when you're done with your loop.
###odbcCloseAll()
i might be wrong. 