R help - Feb 2012 - looping over string of frames when importing with 'sqlFetch' from a Microsoft Access database

Dear R-list,

I am trying to import (all) frames from a Microsoft Access database as
individual data frames in a fancy loop, but I'm having troubles figuring out
how to use the 'sqlFetch' from the RODBS package in a loop (mostly
because I can't figure out how to loop over elements (I came from stata)

I would very much appreciate if anyone on the list could help me solve this
problem, as it is an issue of connecting to a database I can't really make a
working example, please bear with me.

### not-working R code ###

##?first I establish a connection to my database
mdbConnect<-odbcConnectAccess("C:\\... \\database.mdb")

## then I read of all the table names
stringTables <- sqlTables(mdbConnect,
tableType=c("TABLE"))$TABLE_NAME

## and then I meet the wall ...
for(i.Frame in stringTables) {
?? ?i.Frame <- sqlFetch(mdbConnect, i.Frame)
}
## this broken loop creates one data frame called containing the
'i.Frame' containing the last frame in the 'stringTables.'
I'm not doing this correct.

## the final step.
DF <- stringTables[[1]]
for ( .df in stringTables) {
? DF <-merge(DF,.df, by.x="uniqueid", by.y="uniqueid",
all=T)
?}

### end of not-working R code ###

Thanks,
Eric

Problem solved thanks to Peter Langfelder's response to Adel ESSAFI.

This is what should be in the loop,

dfn = list();
for (i in length(stringTables) {
dfn[[ <http://piratepad.net/i>i <http://piratepad.net/i>]] <-
sqlFetch(mdbConnect, stringTables[[
<http://piratepad.net/i>i<http://piratepad.net/i>
]])
}

Thanks,
Eric

On Fri, Feb 24, 2012 at 3:19 PM, Eric Fail <eric.fail@gmx.us> wrote:
> Dear R-list,
>
> I am trying to import (all) frames from a Microsoft Access database as
> individual data frames in a fancy loop, but I'm having troubles
figuring
> out how to use the 'sqlFetch' from the RODBS package in a loop
(mostly
> because I can't figure out how to loop over elements (I came from
stata)
>
> I would very much appreciate if anyone on the list could help me solve
> this problem, as it is an issue of connecting to a database I can't
really
> make a working example, please bear with me.
>
> ### not-working R code ###
>
> ## first I establish a connection to my database
> mdbConnect<-odbcConnectAccess("C:\\... \\database.mdb")
>
> ## then I read of all the table names
> stringTables <- sqlTables(mdbConnect,
tableType=c("TABLE"))$TABLE_NAME
>
> ## and then I meet the wall ...
> for(i.Frame in stringTables) {
>     i.Frame <- sqlFetch(mdbConnect, i.Frame)
> }
> ## this broken loop creates one data frame called containing the
'i.Frame'
> containing the last frame in the 'stringTables.' I'm not doing
this correct.
>
> ## the final step.
> DF <- stringTables[[1]]
> for ( .df in stringTables) {
>   DF <-merge(DF,.df, by.x="uniqueid",
by.y="uniqueid", all=T)
>  }
>
> ### end of not-working R code ###
>
> Thanks,
> Eric
>
> ______________________________________________
> R-help@r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
	[[alternative HTML version deleted]]