[IT] Connecting PHP with Microsoft Access (.mdb or .accdb)

WHAT I USED:
1. XAMPP 2.5 (PHP 5.3.8)
2. Microsoft Access 2007


STEPS:
1. Prepare the database on Microsoft Access. Here, I used 2002-2003 format (.mdb).

(picture 1: a first  Microsoft Access 2007 interface)

(picture 2: preparing a new database named phptoacc.mdb)

(picture 3: preparing new table named testdb with its fields, testid and testname)

(picture 4: inserting two new records)

2. Save the database where the PHP can easily reach it. Here, I saved it on "access" folder inside "htdocs" folder of "xampp" folder.

(picture 5: where I stored my database)

3. Type the PHP script for trying to connect to the database.

<?php $db = $_SERVER["DOCUMENT_ROOT"] ."/access/phptoacc.mdb"; if (!file_exists($db)) {        die("No database file."); } $dbNew = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=$db; Uid=; Pwd=;"); $sql = "select * from testdb"; $rs = $dbNew->query($sql); while($result = $rs->fetch()) {      echo $result[0].": ".$result[1]."<br />"; } ?>

I will explain each part of the script:

a. SELECTING DATABASE

$db = $_SERVER["DOCUMENT_ROOT"] ."/access/phptoacc.mdb"; if (!file_exists($db)) {         die("No database file."); }

The above script is used to validate whether the phptoacc,mdb exists on "access" folder.


b. CONNECTING TO THE DATABASE

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

The above script uses the PDO driver to connect to Microsoft Access.



NOTE:
If the error message show "PDOException Could not find driver", you must check whether the PDO ODBC driver is installed or not. You can check it by executing:

<?php  phpinfo() ?>

 The output should show information like:

PDO

PDO support	enabled
PDO drivers	mssql, mysql, odbc, sqlite, sqlite2

pdo_mysql

PDO Driver for MySQL	enabled
Client API version	mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $

PDO_ODBC

PDO Driver for ODBC (Win32)	enabled
ODBC Connection Pooling	Enabled, strict matching

If an entry for PDO ODBC is not present, you will need to ensure your installation includes the PDO extension and ODBC drivers. To do so on Windows, uncomment the line extension=php_pdo_odbc.dll in php.ini, restart Apache (or XAMPP), and then try to connect to the database again. (source: http://www.sitepoint.com/using-an-access-database-with-php/)

c. THE SQL TO SHOW THE RECORDS

$sql = "select * from testdb"; $rs = $dbNew->query($sql); while($result = $rs->fetch()) {      echo $result[0].": ".$result[1]."<br />"; }

The above script is used to show the records stored on testdb table.



5. Execute the script.

(picture 6: the result on Google Chrome)

Read More