[R] sqlSave [in RODBC]

[Prof Brian Ripley]

You can make use of the 'varTypes' argument of sqlSave() to specify the 
field types to be used.  You will have to do so if you are creating a 
table, as RODBC does not know what Access uses for dates (and POSIXct is 
appropriate for timestamps, not dates).

It is often easier to create the table in the DBMS (here Access) and then 
save to it, and you just haven't told us enough to know if that is what 
you are doing.

On Sun, 19 Feb 2006, Yi-Xiong Zhou wrote:

> Hi,
>
> I am having trouble to write/create a table, which has
> a date field. I want to create a stock price table,
> which has fields of ticker, date, price. First, I
> created such a table in Microsoft Access with a few
> rows inputs. Using sqlQuery, I found that the date
> field was retrieved as POSIXct value. Then I made a
> data.frame with POSIXct as the data type for dates.
> However, I received the following errors when I was
> executing the sqlSave:
>
>> price = data.frame(ticker=rep("FMDEX",5))
>> price$date=c(as.POSIXct("2003-1-1"),
> as.POSIXct("2003-1-2"), as.POSIXct("2003-1-3"),
> as.POSIXct("2003-1-4"), as.POSIXct("2003-1-5"))
>> price$price=1:5
>> price
>  ticker       date price
> 1  FMDEX 2003-01-01     1
> 2  FMDEX 2003-01-02     2
> 3  FMDEX 2003-01-03     3
> 4  FMDEX 2003-01-04     4
> 5  FMDEX 2003-01-05     5
>> sqlSave(h, price, rownames=F)
> Error in sqlSave(h, price, rownames = F) :
>        [RODBC] ERROR: Could not SQLExecDirect
> 37000 -3553 [Microsoft][ODBC Microsoft Access Driver]
> Syntax error in field definition.
>
>
> I am using R2.2.1 with RODBC library, on a Dell P5
> computer with winXP Pro and office 2003.
>
> Thanks for your helps.
>
> Sean
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
>

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595