[R] R crashes when using the RODBC Package

[Bill Cunliffe]

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)