[R-sig-Debian] How to open an access mdb using the rodbc package under Ubuntu 22.04

[Chris Evans]

----- Original Message -----
> From: "Dirk Eddelbuettel" <edd using debian.org>
> To: "Ivan Krylov" <krylov.r00t using gmail.com>
> Cc: "Chris Evans" <chrishold using psyctc.org>, "r-sig-debian" <r-sig-debian using r-project.org>
> Sent: Friday, 17 June, 2022 13:39:59
> Subject: Re: [R-sig-Debian] How to open an access mdb using the rodbc package under Ubuntu 22.04

> Postscriptum: We absolutely don't mind helping here, use on Ubuntu is *very
> much* on charter for r-sig-deb (which is, loosely, for help with any
> .deb-based OS and R).

OK
 
> However, the question really had little to do with the OS and a lot with 'how
> do I do OBDC' which Ivan answered admirably. Next time, though, remember that
> there is r-sig-db focussed on _R and databases_.

Archives show nothing there since Q3 2020 and nothing from it showed up in any of
my searching.

I'm sorry if I'm battering the wrong list but my experience has been that things about databases
can be very specific to the OS.

I don't want to batter more as that's clearly not what you want but for the record here, neither
of Ivan's suggestions (gratefully received) work.

The odbc.ini, which appears to open a connection but shows no tables.  However, I know the tables are in the
file, for instance mdb-tables command from the mdbtools package shows the tables there and I have the paths
and permissions correct, I've checked.

Nor does the direct connection idea:

channel <- odbcDriverConnect('DSN=MDB;Database=EOS.mdb')

work.  That gives me:

Warning messages:
1: In odbcDriverConnect("DSN=MDB;Database=EOS.mdb") :
  [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found and no default driver specified
2: In odbcDriverConnect("DSN=MDB;Database=EOS.mdb") :
  ODBC connection failed

(bit more than a warning to my mind!)

Having spent quite a lot of hours on this, admittedly some of them reminding myself how postgres/psql 
and SQL commands work I am going to retire hurt (!)  Tomorrow I will swallow my FLOSS preferences and 
and remind myself how do this using the RODBC package in R in Windoze and gone through the M$ hoops 
to get the pertinent ODBC driver installed there.  Then I'll pull everything into R, and use save.image() 
there and come back to Linux and go forward after a load()!

I'm just surprised there isn't a more easily accessed and documented way to do this in Linux.

If anyone is confident of an answer (and I appreciate Ivan making it very clear this is not his arena but still 
helping and don't want to abuse him more!), then I'd be grateful to take up advice off list.


> 
> Dirk
> 
> --
> dirk.eddelbuettel.com | @eddelbuettel | edd using debian.org

-- 
Chris Evans (he/him) <chris using psyctc.org> 
Visiting Professor, UDLA, Quito, Ecuador & Honorary Professor, University of Roehampton, London, UK.
Work web site: https://www.psyctc.org/psyctc/ 
CORE site:     https://www.coresystemtrust.org.uk/
Personal site: https://www.psyctc.org/pelerinage2016/
OMbook:        https://ombook.psyctc.org/book/