On Apr 26, 2010, at 12:40 PM, Marc Schwartz wrote:
> 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.
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
______________________________________________
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.
