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

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 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