[R] "[RODBC] ERROR: Could not SQLExecDirect"

[chao gai]

Ruixin,

I think there are a number of issues here:

FIrst of all, ODBC is data base talk. In a data base there are ususally a 
number of tables. Hence you should tell R which table you want to read.
the function sqlTables can tell you which tables are available.

Second, I seem to remember that the $ in some of these table names caused me 
some trouble
Perhaps 
sqlFetch(cnct,'Sheet1') or sqlFetch(cnct,'Sheet1$') could do the trick and 
avoid you the query

Third, it seems that the driver is determining variable types and passing 
these to R. It is not very good at that. You might have unexpected results.

The bright side is, SQL/ODBC is powerfull. After using named regions in my 
Excel sheetI had the following query running  :)

oCE <- odbcConnectExcel('blockdata.2.xls')

#sqlTables(oCE)
# keep for reference
q1 <- sqlQuery(oCE,'select * from linksblok , ((select * from blok1 union 
select * from blok2 union select * from blok3 union select * from blok4 union 
select * from blok5 union select * from blok6) as dit),  rechtsblok,  seco 
where linksblok.mainid=dit.mainid and linksblok.mainid=rechtsblok.mainid and 
dit.oldmonscode=seco.oldmonscode ')



On Thursday 24 May 2007 04:43, Ruixin ZHU wrote:
> Hi, everyone,
>
> I try to run as follows:
> Z>library("RODBC")
> Z>cnct<-odbcConnectExcel("Forbes2000.xls")
> Z>cnct
> RODB Connection 1
> Details:
>   case=nochange
>   DBQ=C:\Program Files\R\R-2.5.0\Forbes2000.xls
>   DefaultDir=C:\Program Files\R\R-2.5.0
>   Driver={Microsoft Excel Driver (*.xls)}
>   DriverId=790
>   MaxBufferSize=2048
>   PageTimeout=5
> Z>sqlQuery(cnct, "select * from \"Forbes2000\\$\"")
>
> However, I got error like this:
> [1] "[RODBC] ERROR: Could not SQLExecDirect"
>
> [2] "42S02 -1305 [Microsoft][ODBC Excel Driver] Microsoft Jet
> Êý¾Ý¿âÒýÇæ
> ÕÒ²»µ½¶ÔÏó'Forbes2000\\$'¡£ÇëÈ·¶¨¶ÔÏóÊÇ·ñ´æÔÚ£¬²¢Õý
>È·µØд³öËüµÄÃû³ÆºÍ· ¾¶¡£"
>
> I do can find Forbes2000.xls in directory, C:\Program Files\R\R-2.5.0.
> Would anybody help me out?
>
> Thanks for any suggestions!
> _____________________________________________
> Dr.Ruixin ZHU
> Shanghai Center for Bioinformation Technology
> rxzhu at scbit.org
> zhurx at mail.sioc.ac.cn
> 86-21-13040647832
>
>
> 	[[alternative HTML version deleted]]