Frustrations with "Connections" and Microsoft Access "MDB" files on Linux

linuxvswindows

#1

I spent a week trying to connect to a Microsoft Access “MDB” file on Linux via the “Connections” tab. I never did get it working; I ended up running the procedure on Windows. So for the benefit of folks who may be going down this path, I’m sharing the details.

The task was to read an MDB file and copy its tables to a PostgreSQL database. On Linux, to read an MDB file via ODBC, you need Linux packages mdbtools and unixodbc. mdbtools has a GUI and is fairly well documented. Moreover, it works as advertised from the command line.

The same cannot be said for unixodbc. The documentation is old, and the official GUI seems not to be maintained. After trying everything I found online I was unable to assign a working ODBC data source name to the MDB file using any of the documentation on any of the major Linux distros - Ubuntu 16.04 LTS and 17.10, Fedora 27, Arch Linux and openSUSE Tumbleweed.

On the other hand, PostgreSQL works, both with odbc and with RPostgres. So does SQLite. And if you really must do this on Linux, the mdb.get function in the Hmisc package works to read the MDB file. But unixodbc and MDB files don’t seem to play together.