Chris Evans
2022-Jun-17 10:01 UTC
[R-sig-Debian] How to open an access mdb using the rodbc package under Ubuntu 22.04
Thanks Ivan: in passing, I've found your many inputs here and on r-help wonderful: thank you. ----- Original Message -----> From: "Ivan Krylov" <krylov.r00t at gmail.com> > To: "Chris Evans" <chrishold at psyctc.org> > Cc: "r-sig-debian" <r-sig-debian at r-project.org> > Sent: Friday, 17 June, 2022 10:41:14 > Subject: Re: [R-sig-Debian] How to open an access mdb using the rodbc package under Ubuntu 22.04> 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.htmlAh, perhaps I should have said that I try to avoid proprietary s'ware if possible and ?899 is out of my range by well perhaps ?890! I could use their 15 day free offer I guess but I'd prefer to find, and document somewhere, a FLOSS solution that goes all the way from having R on Ubuntu to having the RODBC connection work. I am finding bits of that pipeline explained but not all ...> > 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.OK but I didn't see anything there about how I would get RODBC to use that to connect to the mdb file. Sorry if I'm being stupid, not for want of searching or trying! If I can find a FLOSS way of doing it, I _will_ document it at https://www.psyctc.org/Rblog/ for others who may find this challenging as I have. Any further advice or pointers out there?! TIA! Chris> > -- > Best regards, > Ivan-- 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/
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