On Apr 26, 2010, at 12:11 PM, boris.vasil...@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
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
