[R] RODBC Excel sqlQuery insert into

[Jens Scheidtmann]

toby_marks at americancentury.com writes:

> I have searched the archives for using insert into to update spreadsheets 
> using RODBC and have come up short. So, first off, is it possible?
>
> I have put together a dummy xls table (c:\foo.xls)for exploring 
> possibilities of RODBC.  Ultimately, I am interested in replacing much of 
> our previous use of vba macros with R ( I'd prefer elimination, but will 
> take what I can get ).  In order to achieve this, I will still have a need 
> to update spreadsheets directly through R scripts. 
>
> Simple queries seem to work fantastic!  But, I am missing something when 
> it comes to writing.
>
> sqlQuery( myChan,"insert into [my customers$] ( CUST_ID, NAME, SOCIAL ) 
> VALUES( 5,'robin',5678 ) " )
> [1] "[RODBC] ERROR: Could not SQLExecDirect"      "S1000 -3035 
> [Microsoft][ODBC Excel Driver] Operation must use an updateable query."

[...]
>   Driver={Microsoft Excel Driver (*.xls)}

[...]
>
> Additional reference: 
> http://www.microsoft.com/technet/scriptcenter/resources/officetips/jun05/tips0607.mspx

Your link describes the "Microsoft Jet OLE DB Provider" driver,
while your plain ODBC connections to Excel uses the "Microsoft OLE DB
Provider for ODBC Drivers" driver.

The documentation located here 
http://support.microsoft.com/?scid=kb%3Ben-us%3B257819&x=9&y=17
states:

> IMPORTANT: An ODBC connection to Excel is read-only by default. Your
> ADO Recordset LockType property setting does not override this
> connection-level setting. You must set ReadOnly to False in your
> connection string or your DSN configuration if you want to edit your
> data. Otherwise, you receive the following error message: Operation
> must use an updateable query.

Jens