stefan.petersson at inizio.se
2008-Sep-24 14:03 UTC
[R] Error results from MS Access via RODBC
I have an MS Access database with one table and one column holding rep(1:10) I use: library(RODBC) channel <- odbcConnect("test") sqlQuery(channel, paste("SELECT col FROM tblTest"), believeNRows=FALSE) and get: 49 50 51 52 53 54 55 56 57 12337 What? The above should, of course, read: 1 2 3 4 5 6 7 8 9 10 I tried the 'odbcQuery' / 'sqlGetResults' dance. But no love... From a console, 'mdb-sql' and 'isql' gives correct output, so the odbc seems to be working OK. Text columns show up fine when I select them with the above RODBC procedure. I've fiddled with pretty much all the options that go with 'sqlQuery' with the same output every time. I've compacted and repaired the database. I've created other mdb's, with the same result. When I connect to other DB's via RODBC, like MySQL, data is correct... What am I missing? Some output: -------------------------- ~# dpkg -l libmdb* mdbtools ii libmdbodbc 0.5.99.0.6pre1.0.20 MDB tools ODBC module ii libmdbtools 0.5.99.0.6pre1.0.20 mdbtools libraries ii mdbtools 0.5.99.0.6pre1.0.20 JET / MS Access database (MDB) tools -------------------------- ~# mdb-ver db.mdb JET4 -------------------------- R> odbcGetInfo(channel) DBMS_Name "" DBMS_Ver "" Driver_ODBC_Ver "" Data_Source_Name "test" Driver_Name "test" Driver_Ver "test" ODBC_Ver "03.52" Server_Name "03.52" -------------------------- R> version platform: i486-pc-linux-gnu arch: i486 os: linux-gnu system: i486, linux-gnu status major: 2 minor: 7.2 year: 2008 month: 08 day: 25 svn rev: 46428 language: R version.string R version 2.7.2 (2008-08-25) -------------------------- R> sessionInfo() R version 2.7.2 (2008-08-25) i486-pc-linux-gnu locale: LC_CTYPE=en_US.UTF-8;LC_NUMERIC=C;LC_TIME=en_US.UTF-8;LC_COLLATE=en_US.UTF-8;LC_MONETARY=C;LC_MESSAGES=en_US.UTF-8;LC_PAPER=en_US.UTF-8;LC_NAME=C;LC_ADDRESS=C;LC_TELEPHONE=C;LC_MEASUREMENT=en_US.UTF-8;LC_IDENTIFICATION=C attached base packages: [1] stats graphics grDevices utils datasets methods base other attached packages: [1] RODBC_1.2-3