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

[Ivan Krylov]

В Fri, 17 Jun 2022 10:01:28 +0000 (UTC)
Chris Evans <chrishold using psyctc.org> пишет:

> Thanks Ivan: in passing, I've found your many inputs here and on
> r-help wonderful: thank you.

Glad I was able to help!

> OK but I didn't see anything there about how I would get RODBC to use
> that to connect to the mdb file. 

Admittedly, it's not well documented (and the INSTALL file that the
README promises to contain more information is missing). I've never
used ODBC myself, so a lot of what follows is guesswork.

Searching for mdbtools in Debian archive, I find the odbc-mdbtools
package, which contains the driver. Once it's installed, you need to
list the driver in the odbcinst.ini file:

[MDB]
Description=Access database (Unicode)
Driver=libmdbodbcW.so

...and list the database in ~/.odbc.ini:

[EOS]
Description=EOS database
Driver=MDB
Database=/path/to/file.mdb

(If I'm reading [1] correctly, the parameter specifying the path to the
mdb file is called "Database".)

The odbcinst [2] tool might be able to help you add the sections, but
its man page looks somewhat obscure. There might be some magic in the
Debian packages that automatically lists the driver (but not the
database) in the relevant configuration files.

After the sections are present, RODBC::odbcConnect('EOS') should be
able to open the database, or at least provide an error message that
would hopefully shine some light on the problem.

Or maybe it's enough to list the driver in ~/.odbc.ini and then provide
the path to the database in the connection string?

[MDB]
Description=Access database (Unicode)
Driver=libmdbodbcW.so

channel <- odbcDriverConnect('DSN=MDB;Database=/path/to/file.mdb')

You might also have to pass the DBMSEncoding="UCS-2" argument to the
ODBC connection functions to let R know the encoding that the database
engine uses. Or use the other driver file (which is supposed to use
ANSI) and specify a single-byte encoding. (CP-1252?)

-- 
Best regards,
Ivan

[1]
https://github.com/mdbtools/mdbtools/blob/dev/src/odbc/getproperties.c

[2]
https://manpages.debian.org/bullseye/odbcinst/odbcinst.1.en.html