Hi,
I am running the RODBC examples form the help guide. I am trying to
UPDATE a table in an Access data base but I am having an error.
library(RODBC)
library(termstrc)
path = getwd()
setwd(getwd())
dbName = "data.mdb"
pathdbname = paste(path,"/",dbName,sep="")
accesChannel = odbcConnectAccess(pathdbname, uid = "", pwd =
"")
sqlSave(accesChannel, USArrests, rownames = "state", addPK=TRUE)
sqlFetch(accesChannel , "USArrests", rownames = "state") #
get the lot
foo <- cbind(state=row.names(USArrests), USArrests)[1:3, c(1,3)]
foo[1:3,2] <- 9999
sqlUpdate(accesChannel , foo, "USArrests")
The sqlSave and sqlFetch command seem to work fine.
> foo
state Assault
Alabama Alabama 9999
Alaska Alaska 9999
Arizona Arizona 9999
> sqlUpdate(accesChannel , foo, "USArrests")
Error in sqlUpdate(accesChannel, foo, "USArrests") :
cannot update 'USArrests' without unique column
>
I am using R 2.12.0(2010-10-15)
Using Microsoft access 2003.
Furthermore, the sqlColumns(accesChannel , "USArrests") returns the
following information
> sqlColumns(accesChannel , "USArrests")
TABLE_CAT TABLE_SCHEM
TABLE_NAME
1 C:\\ARTIFICALDESKTOP\\CurrentDownloads\\termstrc\\data <NA>
USArrests
2 C:\\ARTIFICALDESKTOP\\CurrentDownloads\\termstrc\\data <NA>
USArrests
3 C:\\ARTIFICALDESKTOP\\CurrentDownloads\\termstrc\\data <NA>
USArrests
4 C:\\ARTIFICALDESKTOP\\CurrentDownloads\\termstrc\\data <NA>
USArrests
5 C:\\ARTIFICALDESKTOP\\CurrentDownloads\\termstrc\\data <NA>
USArrests
COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH
DECIMAL_DIGITS
1 state 12 VARCHAR 255 510
NA
2 Murder 8 DOUBLE 53 8
NA
3 Assault 4 INTEGER 10 4
0
4 UrbanPop 4 INTEGER 10 4
0
5 Rape 8 DOUBLE 53 8
NA
NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE
SQL_DATETIME_SUB
1 NA 1 <NA> <NA> 12
NA
2 2 1 <NA> <NA> 8
NA
3 10 1 <NA> <NA> 4
NA
4 10 1 <NA> <NA> 4
NA
5 2 1 <NA> <NA> 8
NA
CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE ORDINAL
1 510 1 YES 1
2 NA 2 YES 2
3 NA 3 YES 3
4 NA 4 YES 4
5 NA 5 YES 5
>
Any ideas as of what might I have missed?
Thanks,
Jorge
[[alternative HTML version deleted]]