Boris.Vasiliev at forces.gc.ca
2010-Apr-26 17:11 UTC
[R] problems accessing MS Access 2003 database with RODBC
Dear users, I am trying to access a Microsoft Access database from R using RODBC package but I have had little success. The setup works with isql, RODBC seems to connect to the database, but RODBC does not recognize the data in the database. Can anybody advise where I am going wrong? I am using R version 2.10.1 on Ubuntu 8.04. ODBC version is 2.2.11. Mdbtools version is 0.6pre1. RODBC version is 1.3.1. Test database with one table was created in MS Access 2003. The ODBC configuration files are /etc/odbcinst.ini: [Microsoft Access Driver (*.mdb)] Description = MDB Tools ODBC drivers Driver = /usr/lib/libmdbodbc.so.0 Setup FileUsage = 1 CPTimeout = CRReuse /home/vasiliev/.odbc.ini: [test_db] Description = test events database Driver = Microsoft Access Driver (*.mdb) Database = /home/vasiliev/siginci/data/test_db.mdb Trace = Yes TraceFile = /home/vasiliev/odbc.log When I test the set-up with isql it seems to work: isql -v -m10 test_db +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> help +-----------+-----------+-----------+-----------+-----------+ | TABLE_CAT | TABLE_SCHE| TABLE_NAME| TABLE_TYPE| REMARKS | +-----------+-----------+-----------+-----------+-----------+ | | | MSysObject| SYSTEM TAB| | | | | MSysACEs | SYSTEM TAB| | | | | MSysQuerie| SYSTEM TAB| | | | | MSysRelati| SYSTEM TAB| | | | | MSysAccess| SYSTEM TAB| | | | | tblA1 | TABLE | | | | | MSysAccess| SYSTEM TAB| | +-----------+-----------+-----------+-----------+-----------+ SQLRowCount returns 7 7 rows fetched SQL> help tblA1 +-----------+-----------+-----------+-----------+----------+-----------+ -----------+ | TABLE_CAT | TABLE_SCHE| TABLE_NAME| COLUMN_NAM| DATA_TYPE| TYPE_NAME | COLUMN_SIZ| +-----------+-----------+-----------+-----------+----------+-----------+ -----------+ | | | tblA1 | ID | 4 | FIX ME | | | | | tblA1 | Row | 5 | FIX ME | | | | | tblA1 | Value | 4 | FIX ME | | +-----------+-----------+-----------+-----------+----------+-----------+ -----------+ SQLRowCount returns 3 3 rows fetched SQL> select * from tblA1 +-----------+-------+-----------+ | ID | Row | Value | +-----------+-------+-----------+ | 1 | 1 | 2 | | 2 | 10 | 10 | | 3 | 30 | 30 | | 4 | 40 | 40 | +-----------+-------+-----------+ SQLRowCount returns 4 4 rows fetched However, when the connection is opened in R, it appears to be empty. DBMS details are not recognized; table and data are unavailable:> ch <- odbcConnect("test_db") > odbcGetInfo(ch)DBMS_Name DBMS_Ver Driver_ODBC_Ver Data_Source_Name "" "" "" "test_db" Driver_Name Driver_Ver ODBC_Ver Server_Name "test_db" "test_db" "03.52" "03.52"> sqlTables(ch)[1] TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS <0 rows> (or 0-length row.names) Does anybody know what I am doing incorrectly? Sincerely, Boris.
Marc Schwartz
2010-Apr-26 17:40 UTC
[R] problems accessing MS Access 2003 database with RODBC
On Apr 26, 2010, at 12:11 PM, Boris.Vasiliev at forces.gc.ca wrote:> Dear users, > > I am trying to access a Microsoft Access database from R using RODBC > package > but I have had little success. The setup works with isql, RODBC seems > to > connect to the database, but RODBC does not recognize the data in the > database. Can anybody advise where I am going wrong? > > I am using R version 2.10.1 on Ubuntu 8.04. ODBC version is 2.2.11. > Mdbtools > version is 0.6pre1. RODBC version is 1.3.1. Test database with one > table > was created in MS Access 2003. > > The ODBC configuration files are > > /etc/odbcinst.ini: > [Microsoft Access Driver (*.mdb)] > Description = MDB Tools ODBC drivers > Driver = /usr/lib/libmdbodbc.so.0 > Setup > FileUsage = 1 > CPTimeout = > CRReuse > > /home/vasiliev/.odbc.ini: > [test_db] > Description = test events database > Driver = Microsoft Access Driver (*.mdb) > Database = /home/vasiliev/siginci/data/test_db.mdb > Trace = Yes > TraceFile = /home/vasiliev/odbc.log > > When I test the set-up with isql it seems to work: > > isql -v -m10 test_db > +---------------------------------------+ > | Connected! | > | | > | sql-statement | > | help [tablename] | > | quit | > | | > +---------------------------------------+ > SQL> help > +-----------+-----------+-----------+-----------+-----------+ > | TABLE_CAT | TABLE_SCHE| TABLE_NAME| TABLE_TYPE| REMARKS | > +-----------+-----------+-----------+-----------+-----------+ > | | | MSysObject| SYSTEM TAB| | > | | | MSysACEs | SYSTEM TAB| | > | | | MSysQuerie| SYSTEM TAB| | > | | | MSysRelati| SYSTEM TAB| | > | | | MSysAccess| SYSTEM TAB| | > | | | tblA1 | TABLE | | > | | | MSysAccess| SYSTEM TAB| | > +-----------+-----------+-----------+-----------+-----------+ > SQLRowCount returns 7 > 7 rows fetched > SQL> help tblA1 > +-----------+-----------+-----------+-----------+----------+-----------+ > -----------+ > | TABLE_CAT | TABLE_SCHE| TABLE_NAME| COLUMN_NAM| DATA_TYPE| TYPE_NAME | > COLUMN_SIZ| > +-----------+-----------+-----------+-----------+----------+-----------+ > -----------+ > | | | tblA1 | ID | 4 | FIX ME | > | > | | | tblA1 | Row | 5 | FIX ME | > | > | | | tblA1 | Value | 4 | FIX ME | > | > +-----------+-----------+-----------+-----------+----------+-----------+ > -----------+ > SQLRowCount returns 3 > 3 rows fetched > SQL> select * from tblA1 > +-----------+-------+-----------+ > | ID | Row | Value | > +-----------+-------+-----------+ > | 1 | 1 | 2 | > | 2 | 10 | 10 | > | 3 | 30 | 30 | > | 4 | 40 | 40 | > +-----------+-------+-----------+ > SQLRowCount returns 4 > 4 rows fetched > > However, when the connection is opened in R, it appears to be empty. > DBMS details > are not recognized; table and data are unavailable: > >> ch <- odbcConnect("test_db") >> odbcGetInfo(ch) > DBMS_Name DBMS_Ver Driver_ODBC_Ver Data_Source_Name > "" "" "" "test_db" > Driver_Name Driver_Ver ODBC_Ver Server_Name > "test_db" "test_db" "03.52" "03.52" >> sqlTables(ch) > [1] TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS > <0 rows> (or 0-length row.names) > > Does anybody know what I am doing incorrectly? > Sincerely, > Boris.As far as I know, the use of mdb-tools for Access via RODBC on Linux is not supported. A search of the archives reveals this post from Prof. Ripley from 2004: http://tolstoy.newcastle.edu.au/R/help/04/11/6585.html I am presuming that this is still the case, though I am cc:ing Prof. Ripley for confirmation. In that same thread, there is a post from David Whiting that you might find helpful as an alternative, presuming that the information is still of value 6 years hence. HTH, Marc Schwartz