problems accessing MS Access 2003 database with RODBC

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

problems accessing MS Access 2003 database with RODBC

Boris.Vasiliev
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.

______________________________________________
[hidden email] 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.
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: problems accessing MS Access 2003 database with RODBC

Marc Schwartz-3
On Apr 26, 2010, at 12:11 PM, [hidden email] 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

______________________________________________
[hidden email] 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.
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: problems accessing MS Access 2003 database with RODBC

Marc Schwartz-3
On Apr 26, 2010, at 12:40 PM, Marc Schwartz wrote:

> On Apr 26, 2010, at 12:11 PM, [hidden email] 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

______________________________________________
[hidden email] 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.
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: problems accessing MS Access 2003 database with RODBC

Boris.Vasiliev
 

> -----Original Message-----
> From: Marc Schwartz [mailto:[hidden email]]
> Sent: Monday, 26, April, 2010 13:47 PM
> To: Vasiliev B@CEFCOM HQ@Ottawa-Hull
> Cc: [hidden email]; 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, [hidden email] 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.

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.

______________________________________________
[hidden email] 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.
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: problems accessing MS Access 2003 database with RODBC

Marc Schwartz-3
On Apr 28, 2010, at 3:38 PM, [hidden email] wrote:

>
>
>> -----Original Message-----
>> From: Marc Schwartz [mailto:[hidden email]]
>> Sent: Monday, 26, April, 2010 13:47 PM
>> To: Vasiliev B@CEFCOM HQ@Ottawa-Hull
>> Cc: [hidden email]; 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, [hidden email] 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

______________________________________________
[hidden email] 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.
Loading...