PDO: How to access Microsoft Access file on Linux?

Question

I have managed to read data of Microsoft Access file (.accdb) on Windows using PDO but I'm having problem getting to work on Linux (CentOS). I can see modules is installed:

[root@rapid host]# php -m | grep PDO
PDO
PDO_ODBC
[root@rapid host]# php -m | grep odbc
odbc

code:

<?php
    try{
        $dbhAccess = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=/root/access/data.accdb;Uid=Admin");
    }
    catch(PDOException $e){
        echo $e->getMessage();
        exit();
    }

I get an erro when I execute (CLI) PHP file:

[root@rapid host]# php access.php
SQLSTATE[IM002] SQLDriverConnect: 0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified

Answer 1

you should install the MDB driver.

I have no way to try it now, but I think that MDB Tools (specifically the ODBC driver) can do what you' re interested to.

Answer 2

I've never worked with a Microsoft Access database but I do regularly connect to DB2 (on IBM AS/400's) and MS SQL servers from Linux (Ubuntu) servers. Your error seems to indicate you don't have a MS Access driver installed - the only one I'm aware of is: http://www.easysoft.com/products/data_access/odbc-access-driver/index.html

Your error message also says the "Data source name not found" - in order for me to connect to DB2 or MSSQL I have to add some information to /etc/odbc.ini and /etc/odbcinst.ini.

/etc/odbcinst.ini - this is where you describe where to find the drivers for ODBC. Here's an example of what I use for DB2 and MSSQL:

[iseries]
Description     = iSeries Access for Linux ODBC Driver
Driver          = /usr/lib/libcwbodbc.so
Setup           = /usr/lib/libcwbodbcs.so
NOTE1           = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's,
NOTE2           = the following Driver64/Setup64 keywords will provide that support.
Driver64        = /usr/lib/lib64/libcwbodbc.so
Setup64         = /usr/lib/lib64/libcwbodbcs.so
Threading       = 2
DontDLClose     = 1
UsageCount      = 1

# Define where to find the driver for the Free TDS connections.
[freetds]
Description     = MS SQL database access with Free TDS
Driver          = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Setup           = /usr/lib/i386-linux-gnu/odbc/libtdsS.so
UsageCount      = 1

[ODBC]
Trace           = no
TraceFile       = /tmp/odbc.log

In that last section ([ODBC]) I currently have Trace = no - if you change that to Trace = yes you will get some helpful debugging information in the /tmp/odbc.log file.

/etc/odbc.ini - this is where you define your data sources. Here's an example of what I use for DB2 and MSSQL:

[primary]
Description             = primary
Driver                  = iseries
System                  = XXX.XXX.XXX.XXX
UserID                  = XXXXXXXXXX
Password                = XXXXXXXXXX
Naming                  = 0
DefaultLibraries        = QGPL
Database                = MYLIB
ConnectionType          = 0
CommitMode              = 2
ExtendedDynamic         = 0
DefaultPkgLibrary       = QGPL
DefaultPackage          = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression    = 1
LibraryView             = 0
AllowUnsupportedChar    = 0
ForceTranslation        = 0
Trace                   = 0

# Define a connection to the MSSQL server.
# The Description can be whatever we want it to be.
# The Driver value must match what we have defined in /etc/odbcinst.ini
# The Database name must be the name of the database this connection will connect to.
# The ServerName is the name we defined in /etc/freetds/freetds.conf
# The TDS_Version should match what we defined in /etc/freetds/freetds.conf
[mssql]
Description             = MSSQL Server
Driver                  = freetds
Database                = MyDatabase
ServerName              = mssql
TDS_Version             = 8.0

I've seen a number of questions here on StackOverflow talking about using MSFT Access databases from a Linux machine - and there doesn't seem to ever be a happy ending. If there is any way you can port the data to a different, better supported, database system (like MySQL) I think you'll save yourself some headaches. Good luck!

Answer 3

You can not use {Microsoft Access Driver (*.mdb, *.accdb)} as part of your data source because Microsoft do not make an MS Access ODBC driver for Linux. There are 2 ODBC drivers for MS Access as far as I know of. The MDB Tools and the Easysoft ODBC-Access Driver.

If you install either driver you can either use a DSN which you setup in your odbc.ini file or as you prefer a DSN-Less connection. Here is an example of the an Easysoft DSN-Less connection to an MS Access database

PDO("odbc:Driver={Driver=Easysoft ODBC-ACCESS};Dbq=/root/access/data.accdb");

More information on connecting and getting data back using PDO-ODBC can be found on Easysoft PHP guide There is a section about half way down that talks about PDO-ODBC.

Answer 4

import pandas_access as mdb

db_filename = 'my_db.mdb'

# Listing the tables
for tbl in mdb.list_tables(db_filename):
    print(tbl)

# Read a small table
df = mdb.read_table(db_filename, "MyTable")

On Ubuntu you may need to run:

sudo apt install mdbtools

Answer 5

For WINDOWS this is the solution.

After investing a lot of time I have the solution.

The 32-bit version of "AccessDatabaseEngine" must be installed.

Download the 32-bit Access Engine on: https://www.microsoft.com/es-es/download/confirmation.aspx?id=13255

And run it with the command line: AccessDatabaseEngine.exe /passive

After that, it should work.