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. ms-database

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. odbc_pdo_ext
PHP
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
$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.
PHP
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
$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.
PHP
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
$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]; }
  • Fatal error: Uncaught exception"PDOException'with message 'SQLSTATE[im002] SQL.DriverConnect:0[Microsoft][ODBC Driver Manager] data source error found plz help it does not found database give error in php prog line 8
  • I got an error here: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[IM002] SQLDriverConnect: 0 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified' in C:\wamp\www\ODBC\index.php on line 15 Please help
  • Hi, very good the PDO explanatiom, Im having a trouble if the .mdb are out the local machine. I have tryed to map path at local machine but it dosen't connect. If I use c:/mydatabasedir/mydatabase.mdb it Works, but using z:/mydatabase.mdb desen't work. z: is the maped ├írea at another machine. I'm using Windows 7 with wamp/apache/php and the .mdb is at 'z' WINDOWS 2003 SERVER. I opem the machine 'z' for all users but the PDO cant connect. Can you please give me a help? thanks a lot
New question is currently disabled!