[R] problems accessing MS Access 2003 database with RODBC

[Marc Schwartz]

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 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