8

One of our employees created a Microsoft Access Database and has built a Joomla! module around it. It is currently running on a WAMP server, with an ODBC connection to the accdb file.

How can I create an ODBC connection on Ubuntu for the Access database?

At this point, I'm open to having the DB live locally on my Ubuntu server, or on an SMB share somewhere.
Moving it from Access to MySql has already been proposed. It may come down to that, but I'm really being pressured to find another option.

Does anyone know if this is possible and how to accomplish it?

I'm sorry if this question has already been asked; I truly did look for it already, but couldn't find anything specific to connecting Access via ODBC on Linux.

Jens
  • 69,818
  • 15
  • 125
  • 179
Danimal
  • 83
  • 1
  • 1
  • 4
  • Have you tried [pyodbc](http://code.google.com/p/pyodbc/)? – HelloW Nov 16 '12 at 17:01
  • 2
    If you search SO for pyodbc and ubuntu you will get info. – HelloW Nov 16 '12 at 17:06
  • 1
    pyodbc will not be of any help in this case. It needs an ODBC driver, and that exists only on Windows for MS Access. mdbtools seems to be the only way, but eventually, this Access DB will need to be migrated anyway to a real DB like Postgres, MySQL, etc. It will still be also usable from Windows through ODBC. – mivk Jan 30 '17 at 16:11

1 Answers1

10

I'm not entirely sure I understand the use case here, but here are a few thoughts:

  • Access databases are file-based databases and therefore server-less.
    This means that the database file itself can reside on a local Windows machine, a windows network share or a SMB share on a linux box.

  • However, the application that queries the database must have access to the necessary drivers to manipulate the database.
    On Windows, this is not an issue: All Windows version come pre-installed with the Jet drivers for the old .mdb format. For the new .accdb format, you must have one of MSOffice 2007/2010/2013, Access 2007/2010/2013, Access Runtime 2007/2010/2013 or the standalone ACE driver installed to be able to use the new database format.

  • On Linux, the mdbtools package provides some degree of compatibility with the .mdb file format, allowing such a database to be queried and modified by a Linux application directly.
    Another Open Source library (java) for manipulating (.mdb and .accdb) databases is jackcess.

  • ODBC drivers for Linux exist, but as far as I know, they are commercial and can be fairly expensive, see the easysoft Access ODBC Driver.

  • See also this SO question: Connecting to access database from linux

Basically, if you are looking for an easy solution that would be Open Source, going to MySQL or using a local SQLite database will be a lot more workable on Linux.

Community
  • 1
  • 1
Renaud Bompuis
  • 16,596
  • 4
  • 56
  • 86