Re: [R] RODBC, sqlSave and sqlAppend

From: roger bos <roger.bos_at_gmail.com>
Date: Wed 16 Mar 2005 - 06:39:51 EST

I use RODBC all day every day and while I am pretty happy with it, I was never able to make a table separately and append to it using sqlSave. Nevertheless, maybe my observations will help.

I always let sqlSave make the table for me. Make sure the table doesn't exist and it will make it. I never use sqlAppend, I use sqlSave(..., append=TRUE). Also, how do you know you are not getting an error. Try 'go <- sqlSave(...)' and then type go and if it returns 'character(0)' then you have no error, otherwise you will see an extremely brief hint of what went wrong. Finally, I always have my database open when I sqlSave and that is never a problem, so you don't need to close MS access AFAIK. Its probably easier to use sqlQuery right after sqlSave to verify that it worked.

Once sqlSave works, then you can use standard SQL statements to alter the table and alter to columns to make the datatypes whatever you want and then use 'insert into' statements to move the data when you want. So basically, because I save to a temporary table, alter the columns to change the datatypes, and then copy that to my final table. Sounds like a lot of work, but it can be automated once you write the command in R.

Maybe someone will provide you with a better answer, but this might get you started.

Thanks,

Roger

On Tue, 15 Mar 2005 15:19:26 -0000, Matthew W Nash <spjgmwn@iop.kcl.ac.uk> wrote:
> Hi all,
>
> I am currently trying to read, write and append data between R and MS access
> using the RODBC library functions. I have no problems reading in the data
> but when using sqlSave and sqlAppend it doesn't seem to work. I have made
> sure that all the column names are sensible and there are no gaps etc in the
> variables. My call looks like this:
>
> sqlSave(channel,treatlist,test=F)
>
> I've played with various options but what consistently happens is that R
> writes a new table (column names a written and sensible variable types are
> assigned), but it doesn't actually write any data into it. When I run
> sqlAppend there are no error messages, but when I look at the MS access
> database nothing has been written. (I always have the database closed when
> doing this.)
>
> What am I doing wrong?
>
> Matthew Nash,
>
> Post-Doctoral Research Worker,
> GENDEP study,
> SGDP, Institute of Psychiatry,
> PO82, Room CB.15,
> 16 De Crespigny Park,
> London, SE5 8AF,
> United Kingdom
>
> Phone: (+44) 207 848 0805
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> ~
> Announcing the 6th MRC SGDP SUMMER SCHOOL 25 - 29 July 2005 with courses in
> a) Twin model fitting, Mx
> b) Microarrays (Affymetrix), gene expression, SNPs
> c) Linkage, association and allied methods
> http://sgdp.iop.kcl.ac.uk/summerschool/
>
> ______________________________________________
> R-help@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
>



R-help@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 Received on Wed Mar 16 06:50:21 2005

This archive was generated by hypermail 2.1.8 : Fri 03 Mar 2006 - 03:30:48 EST