Skip to content

How to open an access mdb using the rodbc package under Ubuntu 22.04

6 messages · Ivan Krylov, Chris Evans, Dirk Eddelbuettel

Original
Chris Evans
# 
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
Ivan Krylov
# 
On Fri, 17 Jun 2022 08:18:10 +0000 (UTC)
Chris Evans <chrishold at psyctc.org> wrote:

            

      
      
      
    

        
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.

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Chris Evans
    

    

      
      #
    

  


  

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

----- Original Message -----

            

      
      
      
    

        
Ah, 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 ...

            

      
      
      
    

        
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

            

      
      
      
    

        

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Ivan Krylov
    

    

      
      #
    

  


  

      
? Fri, 17 Jun 2022 10:01:28 +0000 (UTC)
Chris Evans <chrishold at psyctc.org> ?????:

            

      
      
      
    

        
Glad I was able to help!

            

      
      
      
    

        
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?)

  
    
  


  


      

    

    

      
      

        


  

        

      Dirk Eddelbuettel
    

    

      
      #
    

  


  

      
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

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Chris Evans
    

    

      
      #
    

  


  

      
----- Original Message -----

            

      
      
      
    

        
OK

            

      
      
      
    

        
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.