R SIG Debian - Jun 2022 - How to open an access mdb using the rodbc package under Ubuntu 22.04

I know Ubuntu isn't Debian but I hope that this list will have the expertise
I need. I have scoured for answers
on the web but they either seem to assume one has ODBCConfig, which doesn't
seem to be available for
Unbuntu 20.04 or 22.04, or they simply don't give me explanations I
understand (and many are quite old).

I want to open a moderately large (830Mb) and moderately complex Access mdb
database into R (4.2.0 (2022-04-22))
I'm running it under Ubuntu 22.04 and have pulled in and compiled unixODBC
and have this from odbcinst -j

unixODBC 2.3.11 
DRIVERS............: /etc/odbcinst.ini 
SYSTEM DATA SOURCES: /etc/odbc.ini 
FILE DATA SOURCES..: /etc/ODBCDataSources 
USER DATA SOURCES..: /root/.odbc.ini 
SQLULEN Size.......: 8 
SQLLEN Size........: 8 
SQLSETPOSIROW Size.: 8

and this in /etc/odbcinst.ini

PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

(That's unchanged from the installation.)

And I seem to have install postgres:

chris at Clevo1:/media/chris/Clevo_SSD2/Music$ sudo su - postgres
[sudo] password for chris: 
postgres at Clevo1:~$ psql
psql (14.3 (Ubuntu 14.3-0ubuntu0.22.04.1))
Type "help" for help.

postgres=# 

I am pretty sure that I need to create a file that contains the instructions
unixODBC needs/uses
to make the mdb file available to RODBC, something along the lines of (from the
odbcConnect help
page):

channel <- odbcConnect("test", uid="ripley",
pwd="secret")

where I think I would replace "test" with the name of that file I have
to create that maps
together EOS.mdb (my mdb file) and the unixODBC driver and I suspect that I
would replace
"ripley" with "chris" and "secret" with the
postgres password for chris.

If I'm correct, it's how to create that mapping file (is it called a DSN
file?) that I need
to know.  However I have a nasty feeling that I may need more than that, e.g.
more work on
unixODBC configuration and/or postgres configuration.

If I have failed to find a resource that explains this, do point me to it!

Thanks in advance,

Chris

-- 
Chris Evans (he/him) <chris at 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/

On Fri, 17 Jun 2022 08:18:10 +0000 (UTC)
Chris Evans <chrishold at psyctc.org> wrote:
> I want to open a moderately large (830Mb) and moderately complex
> Access mdb database into R (4.2.0 (2022-04-22)) I'm running it under
> Ubuntu 22.04 and have pulled in and compiled unixODBC
ODBC needs an Access driver in order to work with Access databases. A
cursory web search gave me this commercial product:
https://www.easysoft.com/products/data_access/odbc-access-driver/index.html

There's also mdbtools <https://github.com/mdbtools/mdbtools>, which
also seems to offer an ODBC driver. It might also be able to let you
convert the Access database for a different SQL engine.

-- 
Best regards,
Ivan