If anyone is interested, I found out that something (Access or ODBC?) didn't like the column name 'Left', after changing it to something else, both sqlSave and sqlUpdate are working as expected.
Thanks to those that replied. 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/ -----Original Message----- From: roger bos [mailto:[EMAIL PROTECTED] Sent: 15 March 2005 19:40 To: [EMAIL PROTECTED] Cc: [email protected] Subject: Re: [R] RODBC, sqlSave and sqlAppend 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 <[EMAIL PROTECTED]> 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/ > > ______________________________________________ > [email protected] mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html > ______________________________________________ [email protected] mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
