Microsoft Access Database using PHP PDO ODBC Example

Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on email

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;
    } 
}
	
		
?>

The Core Tools

Create dashboard for any Database

Data Visualizer and Dashboard Application
SALE
This is the best dashboard software for its price. One good thing we did was to hire their consulting services to build few dashboard prototypes and provide some quick dashboard training.
- Terry Seal, IL
We evaluated Xcelsius and Qlikview and the cost for organization to implement dashboards was quoted over 10,000 USD. For fraction of the above quoted price, we were able to buy the licenses for the web based dashboard software and get some free training. This is truly a dashboard software for small businesses like us.
IT Manager of a Trucking company, OH