databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access & PHP

Introduction to MS Access and PHP

Not so long ago developers would have said that it is near impossible to use an open source programming language with a Microsoft Access database. But these days it is possible. Thanks to ODBC. Open DataBase Connectivity(ODBC) is an Application Programming Interface (API) that allows a programmer to abstract a program from a particular database type or technology. What this means is that you do not have to write separate code for every type of database that you wanted to interact with. All that you do is to write code that uses the ODBC API and it will be sufficient to communicate with as many different databases as needed. In this article we will create a guestbook with a MS Access database backend.

Applications and programming languages required:

  • PHP 5
  • MS Access 2000
  • ODBC-3.51.12

This article assumes that the reader has installed all of the above applications and has at least some basic programming knowledge of PHP.

Creating the database

Start up MS Access and create a new database called guestbook. Then create a table called comments. Add the following fields to the comments table:

The Comments table in design view.

  • The gid field column generates a new entry id for every entry that is made
  • The name field will take the name of the person that makes the entry
  • The comment field takes the comments that are made
  • the entry_date field stores the date that the entry was made.

Creating an ODBC Connection

The next thing we need to do is to create an ODBC connection to our Access database. With an ODBC connection, you can connect to any database, on any computer in your network, as long as an ODBC connection is available.

Here is how to create an ODBC connection to a MS Access Database:

  1. Open the Administrative Tools icon in your Control Panel

    Accessing the Administative Tools from your computers Control Panel
  2. Double-click on the Data Sources (ODBC) icon inside.
  3. Choose the System DSN tab.

    ODBC Data Source Administrator dialog box.
  4. Click on Add in the System DSN tab.
  5. Select the Microsoft Access Driver. Click Finish.

    Creating the New Data Source
  6. In the next screen, click Select to locate the database.
  7. Give the database a Data Source Name (DSN).

    Applying the Data Source Name
  8. Click OK.

Tip: This configuration has to be done on the computer where your web site is located. If you are running Internet Information Server (IIS) on your own computer, the instructions above will work, but if your web site is located on a remote server, you have to have physical access to that server, or ask your web host to set up a DSN for you to use.

Using PHP

Now, let’s retrieve the data from the database using the newly created connection with PHP. Here’s the code:

The PHP code

And here’s the outcome of the above code:

The outcome of the PHP guestbook

The Author

Leidago !Noabeb is a computer programmer based in Namibia. He has worked with both opensource and Microsoft technologies for over seven years and specializes in writing communications software. He has made many contributions to various online websites dedicated to web development. He can be reached at: leidago [at] googlemail.com