Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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.

@shollingsworth

This comment has been minimized.

Copy link

shollingsworth commented Aug 27, 2015

Thanks for putting this out there. It was very helpful.

@lucasleandrodemoura

This comment has been minimized.

Copy link

lucasleandrodemoura commented Mar 18, 2016

Hello.
I followed his example and got the following error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE [00000] SQLDriverConnect: 0' in /var/www/html/application/controllers/importacao.php:45 Stack trace: # 0 / var / www / html / application / controllers / importacao.php (45): PDO -> __ construct ('odbc: Driver = MDB ...')

You have an idea what would be, if he could help me?

@snapey

This comment has been minimized.

Copy link

snapey commented Apr 15, 2016

Thanks for these instructions. They worked well and with standalone php script I can query an MSAccess database. However, under apache, the very first time I try to execute a prepared statement like $result = $sth->execute(Array(':id' => $club_id));, apache dies and logs

ERROR: apport (pid 8637) Fri Apr 15 03:18:36 2016: called for pid 7241, signal 11, core limit 0
ERROR: apport (pid 8637) Fri Apr 15 03:18:36 2016: executable: /usr/sbin/apache2 (command line "/usr/sbin/apache2 -k start")
ERROR: apport (pid 8637) Fri Apr 15 03:18:36 2016: is_closing_session(): no DBUS_SESSION_BUS_ADDRESS in environment
ERROR: apport (pid 8637) Fri Apr 15 03:19:00 2016: wrote report /var/crash/_usr_sbin_apache2.0.crash

Any suggestions please? I hope its not too off-topic its just that in other regards the webserver works fine.

@TSerra-PT

This comment has been minimized.

Copy link

TSerra-PT commented May 19, 2016

Hello,

I implement this driver on my ubuntu 14.04, however in almost query's that i do, the system gives me an error "segmentation fault". sometimes i have this error too "calling mdb_test_sarg on unknown type. add code to mdb_test_sarg() for type 7".
I don't know if it is about the special characters or the huge information that i have on access file...

Any help will be apreciated! :) Thanks

@marlarius

This comment has been minimized.

Copy link

marlarius commented Jun 2, 2016

Thanks. Just what I needed!

@marlarius

This comment has been minimized.

Copy link

marlarius commented Jun 2, 2016

I have played a bit more with it, and it seems to be able to handle only very basic tables. For more complex tables, I get a lot of errors like these:

ERROR: can't find index #7.
CRITICAL: can't find column with internal id 255 in index PrimaryKey
CRITICAL: can't find column with internal id 27903 in index PrimaryKey
CRITICAL: can't find column with internal id 255 in index PrimaryKey

Do you know what the problem is?

@dcantu476

This comment has been minimized.

Copy link

dcantu476 commented Aug 29, 2016

Dude! Thank you for sharing. I've been searching for the solution to getting MDB support in my LAMP stack. Just what I needed.

@marnunez

This comment has been minimized.

Copy link

marnunez commented Dec 9, 2016

@TSerra-PT Did you ever manage to solve the type 7 errors? I'm having those as well and can't find any other mention of this bug before

@JanOonk

This comment has been minimized.

Copy link

JanOonk commented Dec 15, 2016

I followed your great written tutorial on my Ubuntu v14 64bit, on which before it I had a working MSSQL PDO ODBC using unixodbc and freetds, and tried querying several .mdb databases but all gave me an "segmentation fault (core dumped)" error on the most simple queries. It did give me the columnnames though but then it crashes with the above error.

@mauriciopazpp

This comment has been minimized.

Copy link

mauriciopazpp commented Jan 4, 2017

Thanks for these instructions.
I try to execute a huge query, look this...

General error: 20003 Adaptive Server connection timed out [20003]

i'm using linux ubuntu 14, with freetds driver with mssql and laravel (laravel use pdo)

how i can fix this error? someone can help me?

@melonlogic

This comment has been minimized.

Copy link

melonlogic commented Jan 12, 2017

This has been a huge help!! Thank you!

@martinschaepker

This comment has been minimized.

Copy link

martinschaepker commented Apr 14, 2017

how can i connect with this from an Ubuntu to an Windows 7 ? Via SSH Or Network?

@DBARUNNER

This comment has been minimized.

Copy link

DBARUNNER commented May 25, 2017

Hello, I have installed unixODB and libodbc1 in ubuntu 14.0 and have configured the odbc.ini and odbcinst.ini I try to function to connect with database 1- odbc_connect('dns','',''); it through the following error: invalid string or lenth
2 - new /PDO('datasourcename','',''); it through the acces denied for root user but access database file havn't username and password

any help appreciate
thanks

@steinvu

This comment has been minimized.

Copy link

steinvu commented Aug 24, 2017

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!

@scraipt

This comment has been minimized.

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

This comment has been minimized.

Copy link

didinkaj commented Apr 26, 2018

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

This comment has been minimized.

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.