Microsoft Access using PHP PDO

Microsoft Access database is not a preferred choice to store sensitive or large amount of data, because it is not so robust as other database, you will soon discover several limitation as your database size grows. However for small business and individuals Access database is ideal alternative, it is cheap to setup and simple to use! Let’s find out how easily we can play with Access database using PHP PDO.

Let’s create a database file called “database.mdb” using Microsoft Access, and create a table called “users” as shown in picture below. Save the file somewhere within your website.

Connect to Access database File

First we need to connect to database using ODBC, if you are not able to connect and encountered error “could not find driver”, most probably you need to enable “php_pdo_odbc” extension in you PHP.ini file.

$db_username = ''; //username
$db_password = ''; //password
//path to database file
$database_path = "c:/db/path/database.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)}; DBQ=$database_path; Uid=$db_username; Pwd=$db_password;");

Once the PDO object is created, we can now run any SQL queries such as SELECT, UPDATE, INSERT, and DELETE just like MySql, but with some limitation.

SELECT Statement

Let’s say we want to select all records from users table, we can simply run following query and have our records displayed on the browser.

$sql  = "SELECT * FROM users";
$result = $database->query($sql);
while ($row = $result->fetch()) {
    echo $row["u_name"];
    echo $row["u_email"];
    echo $row["u_website"];
}

INSERT Statement

To insert the a record, we can run following query. If successful script will display success message or anything goes wrong error will be displayed.

$sql  = "INSERT INTO users(u_name, u_email, u_website, u_message)
VALUES ('saran','[email protected]','http://www.wwebsite.com','hello how are you')";
if($database->query($sql))
{
	echo 'success';
}else{
	$db_err = $database->errorInfo();
	echo 'Error : ('. $db_err[0] .') -- ' . $db_err[2];
}