Bdtyktl

I needed to be able to read a .mdb (Microsoft Access© (JET) file) copied from a Windows system on Linux, specifically Ubuntu 18.04. I searched dozens of web sites and could not find a straightforward way to achieve this. Most of the pages only discuss how to do this on a Windows system, where naturally the PHP installation comes with support already installed for the standard Microsoft database engines. In particular the page http://php.net/manual/en/pdo.drivers.php does not list "Microsoft Access", or "JET", or ".mdb", or ".accdb" in the description for any of the listed drivers.

For this application it would be excessively cumbersome to demand that the end-users use something like mdbtools to convert the .mdb file to a MySQL/MariaDB/Postgres/SQLite/... database. The user interface has to be to identify the file containing the customer's existing data. The file is then uploaded to the server where its contents are read to initialize or update the server database, but the conversion is not straightforward because the structure of the server SQL database is not identical to that of the Access database, mostly because the DBA who designed this particular Access database assumed that there would never be a requirement for simultaneous access to the database by multiple users. Also like most beginners the DBA used server-managed or "auto-increment" primary keys for almost all of the tables. The exact numeric key values would not be the same in both databases. I do not have a license to run Windows on any of my computers so I cannot test an implementation that runs only on Windows.

Some sites suggest using ODBC as a workaround, so I installed the package php7.2-odbc as follows:

sudo apt install php7.2-odbc

...

Setting up php7.2-odbc (7.2.10-0ubuntu0.18.04.1) ...

Creating config file /etc/php/7.2/mods-available/odbc.ini with new version

Creating config file /etc/php/7.2/mods-available/pdo_odbc.ini with new version

Processing triggers for libapache2-mod-php7.2 (7.2.10-0ubuntu0.18.04.1)

Note that the package to install is not particularly obvious, since all of the web pages I found specified to install either php5-odbc or php7.0-odbc, depending upon how many years ago they were posted, and while it may be obvious to the package maintainers that the driver packages must match the installed version of PHP that is not obvious to the end user who just wants the "right" package installed. This is an issue with everything that needs to be installed to extend PHP. The installation command should provide a way to select the desired packages without forcing the end user to first check to see which version of PHP is running on the system. For example apt-get install php*-odbc.

Just in case I need them in the future I also installed the SQLite and Postgres drivers while the required knowledge was fresh in my mind.

So now the current driver support for PDO on my Ubuntu 18.04 system is summarized as:

php -i | grep PDO    

PDO

PDO support => enabled

PDO drivers => mysql, odbc, pgsql, sqlite

PDO Driver for MySQL => enabled

PDO_ODBC

PDO Driver for ODBC (unixODBC) => enabled

PDO Driver for PostgreSQL => enabled

PDO Driver for SQLite 3.x => enabled

I restarted my Apache server and phpinfo.php shows all of the drivers in place.

So I cribbed some code from a web page:

$db_username = ''; //username

$db_password = ''; //password

// path to database file

$database_path = "/home/jcobban/FamilyTree/Cobban.mdb";

// check file exist before we proceed

if (!file_exists($database_path)) {

    die("Access database file not found !");

}

//create a new PDO object

$database = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=$database_path; Uid=$db_username; Pwd=$db_password;");

$sql  = "SELECT * FROM tableName";

$result = $database->query($sql);

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {

    print_r($row);

}



Fatal error: Uncaught PDOException: SQLSTATE[01000] SQLDriverConnect: 0     [unixODBC][Driver Manager]Can't open lib 'Microsoft Access Driver (*.mdb, *.accdb)' : file not found in /home/jcobban/public_html/testAccess.php:14 Stack trace: #0 /home/jcobban/public_html/testAccess.php(14): PDO->__construct('odbc:DRIVER={Mi...') #1 {main} thrown in /home/jcobban/public_html/testAccess.php on line 14

Note that the file Cobban.mdb does exist at that location since the script did not die at line 7.

The problem seemed to be either that I had not installed the correct driver because I could not find any documentation as to the correct package name to install was, or else every example I could find of what the connection string should be is wrong including https://stackoverflow.com/questions/27066516/microsoft-access-with-php-and-pdo, https://stackoverflow.com/questions/23088127/setting-up-pdo-connection-class-for-microsoft-access-database , etc. In particular I cannot find an authoritative site for connection strings. The site www.connectionstrings.com discusses them only in terms of ActiveX Data Objects (ADO), which do not apply to Linux. The page http://php.net/manual/en/ref.pdo-odbc.php contains only an example of how to use it with MSSQL Server.

I was not encouraged when Connect PHP to Microsoft Access ODBC specifically warns: "There are cookbook recipes all over the place for accessing Jet databases from PHP, but they're red herrings - they all assume you're running PHP on a Windows machine with MS Access installed, and use the Windows-only ODBC driver for opening Jet ("access") files which is bundled with Access. No good to you on Linux."

So I kept plowing away at the web and confirmed that, as described above, ALL of the suggestions and examples in all of the official documentation of PDO and in all of the volunteer supplied pages apply only to Windows. I discovered that I needed to install the following:

sudo apt-get install odbc-mdbtools

sudo apt-get install unixodbc-dev

and change the connection string to specify "Driver=MDBTools;" This is mostly explained in https://gist.github.com/amirkdv/9672857. So I now can read the Access JET file using PDO.

I still cannot find any documentation of how to set up the connection so the obsolete UCS-2LE encoding used in the .mdb file is translated to UTF-8, which I use in my MySQL/MariaDB support. This is part of the lack of documentation of the connection string, where that sort of information is supposed to be specified.