[R] sqlUpdate error with MS acces

[Jorge Nieves]

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