Ivan Krylov
2022-Jun-17 10:48 UTC
[R-sig-Debian] How to open an access mdb using the rodbc package under Ubuntu 22.04
? Fri, 17 Jun 2022 10:01:28 +0000 (UTC) Chris Evans <chrishold at 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
Dirk Eddelbuettel
2022-Jun-17 11:39 UTC
[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). 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_. Dirk -- dirk.eddelbuettel.com | @eddelbuettel | edd at debian.org