Microsoft Access Database using PHP PDO ODBC Example

In this tutorial we will see an example of PHP code on how to connect to MS Access Database using PDO library.

<?php

echo '<pre>';

print_r(PDO::getAvailableDrivers());

echo '</pre>';
?>

Before we proceed make sure the PDO library is enabled. Add the above code into php file and run it in the browser. It will tell you which libraries are currently active

If it is not active, edit the PHP.ini file and locate the PDO line for ODBC

Complete PHP PDO code to query adventure works MS access database

<?php

		
//test_mysql.php?db=infocaptor_server&host=localhost&port=3306&user=root&pass=root
		

// Location to file
$db = 'c:\samples\AdventureWorks.accdb';
$db_param["name"]=$db;
$sql = 'select * from "Purchasing_Vendor"';
if(!file_exists($db)){
	die('Error finding access database');
}
echo "<br>Testing Access PDO";

$content=access_result_pdo_x($db_param,$sql);
print_r($content);

exit;

// Connection to ms access
$db = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=".$db.";Uid=; Pwd=;");

$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
		
		if ($db)
		echo "<br>PDO connection success\n";
		else 
		echo "<br>pdo connection failed\n";

try{
 
 $result = $db->query($sql);
 $row = $result->fetchAll(PDO::FETCH_ASSOC);
 
 print_r($row);
 
}catch(PDOExepction $e){
 echo $e->getMessage();
}
		
	
function access_result_pdo_x($db_param,$sql)
{
 try
 {
 
 echo "Calling access_result_pdo_x<br>";
 if(!file_exists($db_param["name"]))
 {
	throw new RuntimeException('Access Database file path is incorrect or file does not exist.');
 }


	try
	{	
		$cnnt_str="odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=".$db_param["name"].";Uid=; Pwd=;";
		$db = new PDO($cnnt_str);
		echo $cnnt_str;
		$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
	}
	catch (exception $e)
	{
	   print_r($e,true);
   
		$content["error_flag"]="true";
		$content["error_mesg"]="access connection error ". $e->getMessage() ;
		  $content["legends"]=array("Error");
		  $content["data"]=array(0);
		  return $content;
	}
	
 //run the query	
 try
 {
	$stmt=$db->query($sql);
	$stmt->setFetchMode(PDO::FETCH_ASSOC);
 }
 catch (exception $e)
 {
   	$content["error_flag"]="true";
	$err=$db->errorInfo();

	$content["error_mesg"]="access query error ".$e->getMessage();
		  $content["legends"]=array("Error");
		  $content["data"]=array(0);
		  return $content;
 
 }

	 // Loop to handle all results
	 $row_id=0;
	 $column_names=array();
	 $column_types=array();
	 $col_data=array();
	 $row_limit=$db_param['row_limit']; 
	 if (!isset($row_limit) ) $row_limit=10;
	while( ($row = $stmt->fetch()) && $row_id<$row_limit)
	{
	  //$row_data[$row_id]=array();
	   // each key and value can be examined individually as well
	   $col_id=0;
	   //clog(2,print_r($row,true));
	   foreach($row as $key => $value)
	   {
	     if (is_int($key)) continue;
	    // clog(2,"key = {$key} val = {$value}");  

	     $col_data[$col_id][$row_id]=utf8_encode($value); 


	     if ($row_id==0) 
		 {
			//$col_meta=$stmt->getColumnMeta($col_id);
			//xlog(0,"mysql column type =".$col_meta["native_type"]);
		    array_push($column_names,utf8_encode($key)); //just capture column names only once
			array_push($column_types,"VARCHAR");
		 }  
		 $col_id++;
		 //array_push($row_data[$row_id],$value);
		//  print "<br>Key: " . $key . " Value: " . $value;
	   }
	   $row_id++;
	}

	if ($col_id==0)
	{
	  $column_names[]="No Data";
	  $column_types[]="VARCHAR";
	  $col_data[0][0]="no data";

	}
	$content["legends"]=$column_names;
	$content["columnTypes"]=$column_types;
	$content["data"]=$col_data;

    return $content;
 }
    catch (exception $e)
    {
		  $content["legends"]=array("Error");
		  $content["data"]=array(0);
		  $content["error_flag"]="true";
		$content["error_mesg"]="fetch error ".print_r($e,true);
		  return $content;
    } 
}
	
		
?>