Now that we have enabled ODBC by configuring PHP in our last post we can now start reading and writing inside our Microsoft Access database. Before we start coding we need to setup a few things. Firstly we need to add a Microsoft Access data source

 

Creating a Microsoft Access data source

1. Go to your Control Panel and open Administrative Tools
2. If you're on a 32-bit operating system then follow these instructions just open Data Sources (ODBC). But if you are on 64-bit then right-click on Data Sources (ODBC), under the shortcut tab change the Target from %windir%\system32\odbcad32.exe to %windir%\SysWOW64\odbcad32.exe, and then open it.
3. Next, click on System DSN tab and click on Add..
4. You should see a list of your ODBC drivers there. Locate the one labelled Microsoft Access Driver (*.mdb, *.accdb) and then click on Finish. If you do not see this driver Microsoft Access Driver (*.mdb)  should be okay but it will only support MS Access 2003 files only.
   
5. A dialog should appear to ask for name and description. Just type in a name and click on OK

 

Connecting to our database

If you haven't enabled ODBC on you compouter and haven't read the last post on doing so then I suggest you do. If for some reason the drivers aren't installed on your computer then go to http://support.microsoft.com/kb/313008

Now that we have added our data source we can now connect to our Microsoft Access via PHP. Let's get right to it:

<?php

//Get full path to database file [!IMPORTANT]
$database = realpath("demo.mdb"); //MS Access 2007 uses .accdb extension by default

try {
    //Connect to the database by supplying driver, username, password
    $db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ={$database}; Uid=; Pwd=;");

    /* ...... do you crud operations here ...... */
} catch (Exception $e) {
    echo $e->getMessage();
    exit();
}

?>

The code above establishes a connection to our Access database file. Its imperative to include the absolute path otherwise the connection will fail even if the database and your php scripts are in the same folder. We then create a new instance of PDO and give provide our connection parameters (driver, username, password). In the driver parameter you have to type in the driver you picked when you added the data source.

 

Retrieving data from a table

<?php

//Get full path to database file [!IMPORTANT]
$database = realpath("demo.mdb"); //MS Access 2007 = .accdb extension by default

try {
    //Connect to the database by supplying driver, username, password
    $db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ={$database}; Uid=; Pwd=;");

    $SQL = "SELECT * FROM table_name";
    $result = $db->query($SQL); //Run SQL query
    
    while ($row = $result->fetch()) {
        /* ... echo out the values you want ... */
        echo $row['field_name'];
    }
} catch (Exception $e) {
    echo $e->getMessage();
    exit();
}

?>

 

Inserting inside the dabase

<?php

$SQL = "INSERT INTO table_name(field_list) VALUES (file_values)";
$insert = $db->prepare($SQL);

$db->beginTransaction();
$insert->execute();
$db->commit();

?>

Updating values inside the database

<?php

/* ....CONNECTION CODE HERE.... */

$SQL = "UPDATE table_name SET field_name = 'your_value' WHERE pk_field_name = pk_value";
$update = $db->prepare($SQL); //This prevents SQL injection and escapes input
$update->execute();
$affectedRows = $update->rowCount();

?>

Now the tricky thing even with MySQL is when you tend to update with a value that has a quote in it. The function prepare() not only optimizes the query to perform faster, it also makes sure the input is sanitized and safe execute.

 

Deleting rows inside the database

To delete a row inside the MS Access all you have to do is use the code above but just change the SQL statement.

<?php

/* ....CONNECTION CODE HERE.... */

$SQL = "DELETE FROM table_name WHERE pk_field_name = pk_value";
$update = $db->prepare($SQL);
$update->execute();
$affectedRows = $update->rowCount();

?>