Interfacing With MS Access 97+

July 15, 2002

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.

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.

Processing Form "; }

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

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