[R-sig-DB] Error appending data using RODBC, sqlSave()

[Matthew Van Scoyoc]

I need to update tables in an MS Access database, but sqlSave(..., append =
TRUE) keeps returning errors. I've spent the better part of the last hour
on Google, but haven't found an adequate answer. Here's my code.

#+ Munge ----
> # Call in resampled plots from CRC_Data.DB
> resample <- sqlFetch(CRC_Data.DB, "tblResampledPlots")
> glimpse(resample)
Observations: 58
Variables: 7
$ PlotID           (fctr) C057, C085, C091, C128, C133, C233, C284, C311,
C312, C401, C418, ...
$ Re-sampleDate    (time) 2014-07-24, 2014-08-29, 2014-09-27, 2014-10-01,
2014-08-30, 2014-0...
$ Notes            (str) NA, NA, Something important about the plot visit...
$ SppRichStartTime (time) 13:09:00, 11:23:00, 13:54:00...
$ SppRichEndTime   (time) 13:21:00, 11:38:00, 14:15:00...
$ SppRichNotes     (fctr) NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA...
$ SppRichTotalTime (time) 00:12:00, 00:15:00, 00:21:00...
>
> # Call in resampled plots from Summaries.DB
> plots.sum = sqlFetch(Summaries.DB, "tblPlotsResampled")
> glimpse(plots.sum)
Observations: 52
Variables: 3
$ PlotID        (fctr) C021, C057, C085, C091, C128, C133, C233, C284,
C311, C312, C401, C41...
$ Re-sampleDate (time) 2014-08-29, 2014-07-24, 2014-08-29, 2014-09-27,
2014-10-01...
$ Notes         (fctr) NA, NA, NA, Something important about the plot
visit...
>
> # Select the new data
> plots.write = resample %>%
+   filter(!(PlotID %in% plots.sum$PlotID)) %>%
+   select(PlotID:Notes)
> glimpse(plots.write)
Observations: 7
Variables: 3
$ PlotID        (fctr) D098, D104, D105, D605, D607, D608, D649
$ Re-sampleDate (time) 2015-10-14, 2015-09-29, 2015-09-30, 2015-10-16,
2015-10-15...
$ Notes         (fctr) NA, NA, NA, NA, NA, NA, NA
>
> #+ AppendData ----
> sqlSave(Summaries.DB, plots.write, "tblPlotsResampled", append = TRUE)
Error in sqlSave(Summaries.DB, plots.write, "tblPlotsResampled", append =
TRUE) :
  unable to append to table ‘tblPlotsResampled’

Thanks,

MVS
=====
Matthew Van Scoyoc
=====
Think SNOW!

	[[alternative HTML version deleted]]