PHPBuilder.com, the best resource for PHP tutorials, templates, PHP manuals, content management systems, scripts, classes and more.

Sr. Web Developer
mediabistro.com
US-NY-New York

Interfacing With MS Access 97+

Siddharth Venkatesan picture of Siddharth

Before commencing this tutorial, i have to say that for the ODBC setup section, I used the work of Leo West. His page on ODBC setup and interfacing with Ms-SQL server was most usefull to my original work project... Until my boss changed the project specs to make the database to be MS-Access, instead of MySQL. If you need to find out more information about SQL, go here.

Summary of Contents

Creating an Access database
Setting up an ODBC Connection
Interfacing with database

I am going to assume a *little* experience in databases. Having said that, I'm really only hoping that you know how to create a database and insert tables into that database.

Requirements

Apache/Web server of some kind
PHP 3.0 (at least)
Ms-Access (Version 97+)
Wordpad for editing PHP
Windows 9x+ of some working *grin* description

Files used in this tutorial (remove .sid extension to make functional)

I have given directions on exactly how to make the above files. But just in case my directions aren't 100% correct, you can download a working copy of the relevant software.

Important Notes

Throughout this tutorial, i have assumed that the person who will interface with the database will have full read and write priveleges to the database. It is best in any circumstance to make sure you have read and write priveleges to the database. Having said that, please, please, please try out these techniques on a sample database located on a 'local' machine. I know first hand the trouble caused if you screw up a company database upon which most of the company relies.

So, it's onto the tutorial.

Creating An Access Database To Use

Generally when you start up access, it gives you the option of opening an existing database or creating a new one. For the purpose of this tutorial, im going to assume you are going to create a new database.

Start up Access. If Access is already running, close all other Access databases currently open. This is just to make the tutorial a bit easier. Now, create a new Access Database. You can call this database whatever you like. Just make sure you remember the name and the location you save it to, as you will need it later to create the ODBC connection. Into this database create the table 'People'. Into people, add the fields of name and specific types as follows:

Field Name	Field Type
Index	Auto Number
FirstName	Text
LastName	Text
PhoneNumber	Number

You dont need to populate the table as we will do that later on.

Setting Up The ODBC Connection

I previously mentioned Leo West and its from this page that I found out how to create an ODBC link. To create an ODBC link, open up Control Panel and from within control panel, open up ODBC. The icon for this looks like this:

When you open this up, you end up with a screen like this:

Select the System DSN tab. If you havent done any work with user defined DSN's, this should be empty. Select the Add button. You will get a screen which looks like this:

Select the Microsoft Access driver. Click on the finish button. You will now see this screen:

In the Data Source Name text field, enter a name which you wont forget, as this will be the way you will call your database. For the purposes of this tute, enter 'WebTute'. The description text field is for your purposes as you may want to manage your DSN connections later.

Finally, select the Select button. From this menu, choose the Access database you initially created.

And that's it! your ODBC connction is done! Now to interface with it.

Interfacing With Database

Ok, now we can get down to the fun part, interfacing with a database!

We first create 2 php files. One to act as a form to get data from the user and another to actually process the command. I know there are more elegant ways of doing this. However, I was after a way which would be the easiest to understand.

The first PHP file is as follows. It is the form into which the user enters information. You should be able to copy and paste the code into you favourite editor. You can call this file whatever you want. I have called it FormInput.php3.



<?php

function HTML_Head() {
    echo "
    <HTML><HEAD>
    <TITLE>Processing Form</TITLE>
    </HEAD>
    <BODY BGCOLOR=\"#D5D5AB\">";
}

function HTML_Form() {

    echo "
    <FORM NAME = \"AccessInterface\" METHOD=post ACTION=\"DataAccess.php3\">
    Please enter the details you wish to be inserted into the Access Database.<br>
    First Name:<input name=\"FirstName\" TYPE=\"text\" SIZE=\"25\"><br>
    Last Name:<input name=\"LastName\" TYPE=\"text\" SIZE=\"25\"><br>
    Phone Number: <input name=\"PhoneNumber\" TYPE=\"text\" SIZE=\"25\"><br>
    <INPUT type=\"Submit\">
    </form>
    ";
}

function HTML_Existing() {
    echo "Existing database entries";
}

function HTML_Foot() {
    echo "</body></html>";
}

HTML_Head();
HTML_Form();
HTML_Existing();
HTML_Foot();

?>

The code on the prior page is pretty common place. Now we actually interface with the database. We can do this using the inherent ODBC functions within PHP and SQL. As the above form says, the action is going to be DataAccess.php3. So copy and paste the code below into the file DataAccess.php3.



<?php

function HTML_Head() {
    echo "
    <HTML><HEAD>
    <TITLE>Processing Form</TITLE>
    </HEAD>
    <BODY BGCOLOR=\"#D5D5AB\">";
}

function HTML_Foot() {
    echo "</body></html>";
}

function Database_Entries($msg) {
    echo $msg;
}

function Output_Entries() {
    /*
        Make the connection to the database. The syntax is 
        odbc_connect( 'SYSTEM_DSN' , 'USER', 'PASSWORD' );
        $cnx will hold the
        pconnect is used to establish a persistent database 
        connection to the Database until the procedure is completed.
    */

    $cnx = odbc_connect( 'WebTute' , 'root', '' );
    if (!$cnx) {
        Error_handler( "Error in odbc_connect" , $cnx );
    }

    // send a simple odbc query . returns an odbc cursor
    $cur= odbc_exec( $cnx, "select Index,FirstName,LastName,PhoneNumber from People" );
    if (!$cur) {
        Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );
    }
    echo "<table border=1><tr><th>Index</th><th>First Name</th>".
        "<th>Last Name</th><th>Phone Number</th></tr>\n";
    $nbrow=0;   //Local variable to count number of rows

    // fetch the succesive result rows
    while( odbc_fetch_row( $cur ) ) {
        $nbrow++;
        $Index= odbc_result( $cur, 1 ); // get the field "Index"
        $FirstName= odbc_result( $cur, 2 ); // get the field "FirstName"
        $LastName= odbc_result( $cur, 3 ); // get the field "LastName"
        $PhoneNumber= odbc_result( $cur, 4 ); // get the field "PhoneNumber"

        echo "<tr><td>$Index</td><td>$FirstName</td>".
            "<td>$LastName</td><td>$PhoneNumber</td></tr>\n";
    }

    echo "<tr><td colspan=2>$nbrow entries </td></tr></table>";

    // close the connection. important if persistent connection are "On"
    odbc_close( $cnx);
}

function Error_Handler( $msg, $cnx ) {
    echo "$msg \n";
    odbc_close( $cnx);
    exit();
}

function Enter_New_Entry($FirstName,$LastName,$PhoneNumber) {

    /*
        First, we create a connection to our ODBC source. This is done by creating
        a connection. Once this is done, we are returned an ODBC connection number.
        We use this number to use the ODBC functions within PHP.
    */

    $cnx = odbc_connect( 'WebTute' , 'root', '' );
    if (!$cnx) {
        Error_handler( "Error in odbc_connect" , $cnx );
    }
    $SQL_Exec_String = "Insert Into People (FirstName, LastName, PhoneNumber)
            Values ('$FirstName', '$LastName', '$PhoneNumber')";

    $cur= odbc_exec( $cnx, $SQL_Exec_String );
    if (!$cur) {
        Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );
    }

    odbc_close( $cnx);
}

$strOldEntries = "Previous Entries in database";
$strNewEntries = "Updated version of databse (after entries)";

HTML_Head();
Database_Entries($strOldEntries);
Output_Entries();
Enter_New_Entry($FirstName,$LastName,$PhoneNumber);
Database_Entries($strNewEntries);
Output_Entries();
HTML_Foot();

?>

Now you should be ready to go! In this example, i havent done any deletion of records. However, as you can see, if you modify the variable SQL_Exec_String, you can delete specific records as well.

I hope this has been of use to you.

--Sid