[R] problems accessing MS Access 2003 database with RODBC
Marc Schwartz
marc_schwartz at me.com
Wed Apr 28 23:04:37 CEST 2010
On Apr 28, 2010, at 3:38 PM, Boris.Vasiliev at forces.gc.ca wrote:
>
>
>> -----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
>> 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
>>
>>
>
> Marc,
>
> Thank you very much for your suggestions and apologies for tardy reply.
No problem....
> To summarize the discussion, it seems that there are several options to
> access Microsoft Access databases from R on Linux
>
> 1. Use an ODBC driver for Microsoft Access databases other than the
> driver in mdbtools. I think EasySoft offers such driver but it is
> relatively expensive ~400 pounds/licence.
>
> 2. Convert the database into a MySQL database using mdbtools and tools
> build on top of it. Although appealing, this implies existence of two
> databases with the same data.
>
> 3. Use mdb.get() from Hmisc package to import entire tables from the
> database into dataframes.
>
> 4. Switch to R on Windows where ODBC driver for Microsoft Access
> databases is well-behaved and freely available.
>
> For my application, option 3 seems to suit best. My database is
> relatively small (~200 thousand records), has only few tables, and speed
> is not comprised greatly when I import everything into R.
>
> Once again many thanks for your help.
> Regards,
> Boris.
Happy to help and just to close the loop for future archive searches, via an offlist reply, Prof. Ripley did confirm that RODBC does not support the use of mdb-tools, despite his attempts years ago to make it so.
One additional option for non-Windows platforms is on OSX via the Actual Technologies ODBC driver:
http://www.actualtech.com/product_access.php
NB that it is read only (cannot write to Access).
Regards,
Marc
More information about the R-help
mailing list