Interfacing With MS Access 97+

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:

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.

 
    Processing Form 
    "; 
} 

function HTML_Form() { 

    echo " 
    
 
    Please enter the details you wish to be inserted into the Access Database.
 
    First Name:
 
    Last Name:
 
    Phone Number: 
 
     
    
 
    "; 
} 

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

function HTML_Foot() { 
    echo ""; 
} 

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.

 
     
    "; 
} 

function HTML_Foot() { 
    echo ""; 
} 

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 "". 
        "\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 "". 
            "\n"; 
    } 

    echo "
Index
First Name
Last Name
Phone Number
$Index
$FirstName
$LastName
$PhoneNumber
$nbrow entries 
"; 

    // 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.