Microsoft Access database using PHP PDO ODBC sample

In this tutorial , We'll see a on how to use PDO The library is connected to MS Access Database PHP Code example .

<?php

echo '<pre>';

print_r(PDO::getAvailableDrivers());

echo '</pre>';
?>

Before we move on , Please make sure PDO Library enabled . Add the above code to php File and run in the browser . It will tell you which libraries are currently active

If it is not activated , Please edit PHP.ini File and find ODBC Of PDO That's ok

complete PHP PDO Code 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;
    } 
}
	
		
?>