On Apr 28, 2010, at 3:38 PM, boris.vasil...@forces.gc.ca wrote: > > >> -----Original Message----- >> From: Marc Schwartz [mailto:marc_schwa...@me.com] >> Sent: Monday, 26, April, 2010 13:47 PM >> To: Vasiliev b...@cefcom h...@ottawa-hull >> Cc: r-help@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.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 >> >> > > 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 ______________________________________________ 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.
