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 ir. Thierry Onkelinx Instituut voor natuur- en bosonderzoek / Research Institute for Nature and Forest team Biometrie & Kwaliteitszorg / team Biometrics & Quality Assurance Kliniekstraat 25 1070 Anderlecht Belgium + 32 2 525 02 51 + 32 54 43 61 85 Thierry.Onkelinx at inbo.be www.inbo.be To call in the statistician after the experiment is done may be no more than asking him to perform a post-mortem examination: he may be able to say what the experiment died of. ~ Sir Ronald Aylmer Fisher The plural of anecdote is not data. ~ Roger Brinner The combination of some data and an aching desire for an answer does not ensure that a reasonable answer can be extracted from a given body of data. ~ John Tukey * * * * * * * * * * * * * D I S C L A I M E R * * * * * * * * * * * * * Dit bericht en eventuele bijlagen geven enkel de visie van de schrijver weer en binden het INBO onder geen enkel beding, zolang dit bericht niet bevestigd is door een geldig ondertekend document. The views expressed in this message and any annex are purely those of the writer and may not be regarded as stating an official position of INBO, as long as the message is not confirmed by a duly signed document.
It seems that your sqlTables also give no results. So there must be something wrong with the ODBC connect. I didn't use odbcConnectAccess, but made an ODBC connection, and then used ODBCconnect to connect to our database. Maybe you can define a user DSN and try it this way? Bart -- View this message in context: http://r.789695.n4.nabble.com/puzzling-RODBC-error-tp4650837p4650840.html Sent from the R help mailing list archive at Nabble.com.
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.2Some 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