[R] puzzling RODBC error

[Marc Schwartz]

On Nov 26, 2012, at 7:37 AM, "ONKELINX, Thierry" <Thierry.ONKELINX at inbo.be> wrote:

> Dear all,
> 
> I'm trying to connect to an MSAccess database (ArcGIS personal geodatabase). I keep getting an error about the channel when using sqlQuery(). However, sqlTables() does not complain about the channel and lists all tables in the database. If I try sqlFetch(), then R crashes.
> 
> I'm happy to hear suggestions on how to solve this.
> 
> Best regards,
> 
> Thierry
> 
>> MDB <- odbcConnectAccess("//inbodata/indata/Projects/PRJ_Watervogels/Geoloket/Telgebieden watervogeltellingen/Watervogellocaties_copy.mdb")
>> sqlQuery(channnel = MDB, "SELECT gebiedscode FROM ganzengebieden;")
> Error in sqlQuery(channnel = MDB, "SELECT gebiedscode FROM ganzengebieden;") :
>  first argument is not an open RODBC channel
>> sqlTables(channel = MDB) #truncated output!
>   TABLE_SCHEM                    TABLE_NAME   TABLE_TYPE REMARKS
> 13        <NA>                ganzengebieden        TABLE    <NA>
>> sqlFetch(channel = MDB, 'ganzengebieden') #makes R crash
> 
>> sessionInfo()
> R version 2.15.2 (2012-10-26)
> Platform: i386-w64-mingw32/i386 (32-bit)
> 
> locale:
> [1] LC_COLLATE=Dutch_Belgium.1252  LC_CTYPE=Dutch_Belgium.1252
> [3] LC_MONETARY=Dutch_Belgium.1252 LC_NUMERIC=C
> [5] LC_TIME=Dutch_Belgium.1252
> 
> attached base packages:
> [1] stats     graphics  grDevices utils     datasets  methods   base
> 
> other attached packages:
> [1] RODBC_1.3-6
> 
> loaded via a namespace (and not attached):
> [1] tools_2.15.2


Some comments:

1. DB related posts should go to R-SIG-DB:

  https://stat.ethz.ch/mailman/listinfo/r-sig-db

2. You might try to use odbcConnectAccess2007() to see if that provides a resolution.

3. You might also be sure that you don't have a 32/64 bit mis-match between the ODBC drivers, the DSN configuration and Access. I note that you are running 32 bit R on Windows, so perhaps you have already looked into this. There is some additional info on this in the RODBC vignette and some posts in the archives suggesting that you might get other errors, but worth considering if you have not. The crash suggests that something is amiss in the configuration.

Regards,

Marc Schwartz