[R] problems accessing MS Access 2003 database with RODBC

[Marc Schwartz]

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