Hi,

 

I've written some code that fetches data from an Access Database (2003),
processes the data, then saves the modified data back into a table in the
Access database.

 

It works if I only pass through the code once, but if I put a loop around
the code so that I fetch data from a different source table, and then save
it again to a different destination table, the code crashes.  It is either
the final sqlQuery or sqlSave queries.  I have no idea why it is crashing.

 

As an aside, I also have trouble with the odbcCloseAll() command - R tends
to crash with that, also.

 

I'm running this on Windows XP, Access 2003, R version 2.7.2

 

Any insights would be greatly appreciated!  The condensed version of the
code is below.

 

 

# import the libraries

library(RODBC)

 

# Close any existing connections

odbcCloseAll()

 

# Connect to database

channel <- odbcConnect("database", uid="", pwd="")

 

for ( i in 1:2 ) {

 

            # 1. Weekly 2. Monthly

            if (i == 1) {

                        bWeekly <- TRUE

            } else {

                        bWeekly <- FALSE

            }

 

            # Get Prices

            if ( bWeekly ) {

                        tblName <- "qryDataWeekly_Crosstab"

            } else {

                        tblName <- "qryDataMonthly_Crosstab"

            }

            prices <- sqlFetch(channel, tblName, colnames = FALSE, rownames
= TRUE)

 

            # Get Weightings

            sql <- "SELECT BbSecurity, Weighting FROM qryWeightingsUse"

            sWeights <- sqlQuery(channel, sql)

 

            # ==== Copy Data to Database ====

            if ( bWeekly ) {

                        tblName <- "tblIndexWeekly"

            } else {

                        tblName <- "tblIndexMonthly"

            }

            sqlQuery(channel, paste("DELETE * FROM ", tblName)) 

            sqlSave(channel, indexDf, tablename = tblName, append=TRUE,
rownames=FALSE)

 

}

 

odbcClose(channel)

 


	[[alternative HTML version deleted]]


