-----Original Message-----
From: Marc Schwartz [mailto:marc_schwartz at me.com]
Sent: Monday, 26, April, 2010 13:47 PM
To: Vasiliev B at CEFCOM HQ at Ottawa-Hull
Cc: r-help at r-project.org; Ripley Prof Brian
Subject: Re: [R] problems accessing MS Access 2003 database with RODBC
On Apr 26, 2010, at 12:40 PM, Marc Schwartz wrote:
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
package but I have had little success. The setup works with isql,
RODBC seems to connect to the database, but RODBC does not
the data in the database. Can anybody advise where I am
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
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
|
| | | tblA1 | Value | 4
+-----------+-----------+-----------+-----------+----------+--
---------+
-----------+
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
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"
[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:
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.
FYI, I found another possible option which is the mdb.get()
function in Frank Harrell's Hmisc package on CRAN.
Note that at the moment, some of the CRAN network is down:
https://stat.ethz.ch/pipermail/r-help/2010-April/236583.html
HTH,
Marc