Skip to content

Instantly share code, notes, and snippets.

@amirkdv
Last active August 25, 2023 13:14
Show Gist options
  • Save amirkdv/9672857 to your computer and use it in GitHub Desktop.
Save amirkdv/9672857 to your computer and use it in GitHub Desktop.
How to handle MS Access MDB files in Linux with PHP5 PDO and ODBC

To be able to use PHP5 PDO with MS Access mdb files the following is required (the same applies for the PHP4 style of using odbc_X except for the obviously PDO specific requirements):

PHP ODBC module

In Linux this is achieved by intalling the php5-odbc package:

  php -i | grep PDO
  # PDO
  # PDO support => enabled
  # PDO drivers => mysql, odbc
  # PDO Driver for MySQL => enabled
  # PDO_ODBC
  # PDO Driver for ODBC (unixODBC) => enabled

In Windows, typical PHP installation contains ODBC support.

ODBC Configuration

Independent of PHP, the O/S should have the libraries to handle ODBC API. In Linux this is achieved by installing the libodbc1 (formerly libodbc) package. Note that, libodbc1 comes with common distributions of Linux (see PHP configuration above says PDO Driver for ODBC (unixODBC) => enabled). Same applies to Windows.

ODBC uses drivers for each DBMS. Furthermore in ODBC parlance, a connection to a database is made via a Data Source Name. Accordingly, in Linux, ODBC respects two main configuration files:

  • /etc/odbcinst.ini: contains definitions of drivers and general configuration (for example whether odbc should keep logs). Each driver that is installed must update this file with a block that typically looks like the following:

    [MyDriver]
    Description = MyDriver Driver for MyDBMS
    Driver      = /path/to/module.so
    Setup       = /path/to/module.so
    FileUsage   = 1
    UsageCount  = 1
    
  • /etc/odbc.ini: contains configuration for specific data sources. For example:

    [MyDataSource]
    Description = The Source of My Data
    Driver      = MyDriver
    ServerName  = localhost
    Database    = MyDatabase
    UserName    = admin
    Password    = something
    

    Note that the driver name, whatever it may be, must be defined in a corresponding block in /etc/odbcinst.ini.

ODBC MDB driver

In Linux, this driver is provided by the libmdbodbc1 (formerly libmdbodbc) package. Once the package is installed, the following block is added to /etc/odbcinst.ini:

[MDBTools]
Description = MDBTools Driver
Driver      = libmdbodbc.so.1
Setup       = libmdbodbc.so.1
FileUsage   = 1
UsageCount  = 1

This defines the MDBTools driver for ODBC (in some versions the driver name that it creates is called MDBToolsODBC). In Windows, support for MDB files is built in, however the driver name is different:

  • Microsoft Access Driver (*.mdb) in Windows <= XP
  • Microsoft Access Driver (*.mdb, *.accdb) in Windows 7+

(not sure of the latter, but the former does work in Windows XP).

Usage

Having installed the above (i.e libmdbodbc1 and php5-odbc, note that libodbc1 comes with all main distribution of Linux), the following PHP code will work in Ubuntu and Windows XP:

$query = 'SELECT * FROM MyTable';
$mdb_file = '/path/to/example.mdb';
$uname = explode(" ",php_uname());
$os = $uname[0];
switch ($os){
  case 'Windows':
    $driver = '{Microsoft Access Driver (*.mdb)}';
    break;
  case 'Linux':
    $driver = 'MDBTools';
    break;
  default:
    exit("Don't know about this OS");
}
$dataSourceName = "odbc:Driver=$driver;DBQ=$mdb_file;";
$connection = new \PDO($dataSourceName);
$result = $connection->query($query)->fetchAll(\PDO::FETCH_ASSOC);
print_r($result);

Note that the entire dataSourceName definition could have been replaced by odbc:MyDSN if we had created a block in /etc/odbc.ini with equivalent configuration.

Related Packages

  • mdbtools package is a collection of utilities to work with .mdb files. It provides command line utilites such as the following:

    mdb-tables my_access_db.mdb # lists all tables
    
    # print the schema of the given db as per MySQL's dialect of SQL
    mdb-schema -I mysql my_access_db.mdb
    
    # print SQL INSERT statements for the contents of the given table as per
    # MySQL's dialect of SQL
    mdb-export -I mysql my_access_db.mdb my_access_table
    

    Note, however, that mdbtools is completely unnecessary for handling mdb files using PHP. Also note that installing the mdbtools package does not install libmdbodbc1 which is what ODBC needs to operate on mdb files. From the documentation of mdbtools it seems like the driver was first developed as part of mdbtools, but as it stands now, the mdbtools package is irrelevant for enabling ODBC mdb support. Note, also, that the old deb package (available through apt) is simple to install, but the newer GitHub repo chokes on different parts while being built from source, see the repo issue queue).

  • unixodbc: depends on the libodbc1 and provides a set of command line utilies, most importantly isql which is an interactive shell to query an arbitrary ODBC drived database. For example, in the MDB case above, if the /etc/odbc.ini contains the following block:

    [MyMDB]
    Description = The Source of My Data
    Driver      = MDBTools
    ServerName  = localhost
    Database    = /path/to/file.mdb
    

    one can interactively run queries against the above DB by:

    isql -v MyMDB
    

    unixodbc also provides the odbcinst command line tool to configure ODBC drivers. None of these utilities are needed to enable the PHP ODBC driver in Linux.

  • PECL mdbtools extension is a seemingly abondoned PECL extension based on the work of the original mdbtools. Last updated in 2007 (as of Mar 2014), with the only stable release being version 1.0.0 from November 2007. This extension is again irrelevent given the PDO/ODBC usage.

General other notes

  • Common erros are:

    • If PDO is not aware of its ODBC driver, you will get:

      'PDOException' with message 'could not find driver'
      
    • If ODBC fails to find the driver specified, e.g. you use Microsoft Access Driver (*.mdb) in Linux:

      'PDOException' with message 'SQLSTATE[IM002] SQLDriverConnect: 0
      [unixODBC][Driver Manager]Data source name not found, and no default
      driver specified'
      
  • There has been some effort to circumvent the need to interact with MS Access databases:

  • If sample mdb file is needed for testing, try this; it has one table and two columns.

@scraipt
Copy link

scraipt commented Jan 22, 2018

already had to use utf8 in this context?:

$query = 'SELECT * FROM MyTable'; $mdb_file = '/path/to/example.mdb'; $uname = explode(" ",php_uname()); $os = $uname[0]; switch ($os){ case 'Windows': $driver = '{Microsoft Access Driver (*.mdb)}'; break; case 'Linux': $driver = 'MDBTools'; break; default: exit("Don't know about this OS"); } $dataSourceName = "odbc:Driver=$driver;DBQ=$mdb_file;"; $connection = new \PDO($dataSourceName); $result = $connection->query($query)->fetchAll(\PDO::FETCH_ASSOC); print_r($result);

@didinkaj
Copy link

i have a codeignitor project using Access DB how should i update my configuration
$db['zkteco'] = array( 'dsn' => 'Driver=MDBTools};Dbq=file.mdb', 'hostname' => 'Driver=MDBTools};Dbq=file.mdb', 'username' => '', 'password' => '', 'database' => 'Driver={MDBTools};Dbq=file.mdb', 'dbdriver' => '/usr/lib/odbc/libtdsodbc.so', 'Setup' => '/usr/lib/libcwbodbcs.so', 'dbprefix' => '', 'pconnect' => TRUE, 'db_debug' => FALSE, 'cache_on' => FALSE, 'cachedir' => '', 'char_set' => 'utf8', 'dbcollat' => 'utf8_general_ci', 'swap_pre' => '', 'autoinit' => TRUE, 'stricton' => FALSE );

@Sarke
Copy link

Sarke commented Feb 8, 2019

Thanks for the great info!

FYI, the package libmdbodbc1 doesn't exist after Ubuntu 16.04 (xenial), nor does it exist in the current Debian repos either. It was just an alias package anyways, and that's why it was removed. The correct and working package (that libmdbodbc1 depended on) is odbc-mdbtools.

@jlujan2016
Copy link

hello in my case I use xamp do you have any recommendation?

@TatkaVu
Copy link

TatkaVu commented Nov 5, 2020

Hi,
Thanks for the instructions.

Like marlarius I also have the following error: ERROR: can't find index...

It seems the fields are found and displayed but the values aren't displayed for some reason (only the ID value is shown..):

ERROR: can't find index #3.
array (
  0 =>
  array (
    'id' => '84',
    0 => '84',
    'username' => NULL,
    1 => NULL,
  )

Any idea?

Thanks!

I have the same problem as steinvu!

anyone have solution? Many thanks.

@Ogagnon
Copy link

Ogagnon commented Feb 19, 2021

Would any of this allow me to write to an MsAccess DB? I need to insert data in an empty db. Nothing else. But it always gives me the following error as soon as I try any write/update/delete operation.

pyodbc.OperationalError: ('08001', "[08001] Couldn't parse SQL\n (1) (SQLExecDirectW)")

@nogenem
Copy link

nogenem commented Jul 25, 2022

Would any of this allow me to write to an MsAccess DB? I need to insert data in an empty db. Nothing else. But it always gives me the following error as soon as I try any write/update/delete operation.

pyodbc.OperationalError: ('08001', "[08001] Couldn't parse SQL\n (1) (SQLExecDirectW)")

I'm also trying to INSERT data into an access db, but the odbc-mdbtools only supports SELECT, thats why you are getting "Couldn't parse SQL"... ;/ I'm looking for an alternative for the past 2 days, but still couldn't find one 😥

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment