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]]
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]]
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)