Connecting MS Access to the Web

This tutorial will use Apache, PHP and Microsoft Access for database connectivity. PHP and Apache are free but covered under an Open Source Agreement. Instructions on how to install these software packages are provided below.

Install Microsoft Access97 or 2000. Make sure all ODBC drivers are installed.

Apache is a powerful web server and PHP is a powerful scripting language. PHP and Apache tied with many available databases enable powerful Internet and e-commerce solutions for businesses today.

Behind the web page:

You click on a link from your web browser; http://www.IwantanAinthisclass.edu/index.php3.
That sends a request to the web server for the file index.extension (where extension is .cfm, .php3, or .asp )
Your web server has already been configured to know that *.extension is handled by another "interpreting" program. So web server sends the file to that program.
Interpreting program, "reads" page. Processes all commands within page. One command is connection to database, plus get information from database.
Program converts all commands and statements to HTML (or as designed by) programmer.
Then HTML goes back to web server and Web server sends requested page back to back to you.

Installing Apache and PHP

What is PHP? PHP is a server-side, cross-platform, HTML embedded scripting language. http://www.php.net
What is Apache? Apache has been the most popular web server on the Internet since April of 1996 according to the Netcraft Survey. http:// www.apache.org

To install Apache and PHP on the Windows platform read the following tutorial:

http://www.phpbuilder.com/columns/moon19990716.php3

***** Since we will be using Microsoft Access make sure to enable, "extension=php3_odbc.dll" *****

For General Apache and PHP installation, the following tutorials will help:

Developing the database

First thing to do. Develop database. Normalize tables and establish primary keys, foreign keys, etc.
Establish Relationships, Cascate deletes, updates,etc.
Understand what you are trying to retrieve from this database - write example queries using query module

Connecting the database to the Web Server

On Win95, NT, create ODBC Data Source. ( Start -> Settings -> Control Panel -> ODBC Data Sources )
What is ODBC????
Open Database Connectivity (ODBC) is a widely accepted application programming interface (API) for database access.
Basically, instead of programming for different platforms, Access, Oracle, ODBC provides a standard interface to "talk" to the database.
All you need is an ODBC driver for the database you want to use

Windows folks ( sorry MAC users) go to : Control Panel -> ODBC Data Sources Select System DSN

Click Add ...Select Database Driver you want. (This case choose Microsoft Access Driver)

Choose configure -> Type easy to remember name in "Data Source Name", give it a description and then click "Select..." to locate your database. You can also do other things, for example, add password, using the "Advanced ..." button, but we do not need that in this case.

That’s it! You’re done with database setup.

PHP is the actual link to the database. Make sure that your PHP configuration file enables the ODBC.dll. Read the tutorials above to ensure this is correct.

Developing an Interactive Website

Inserting information into a database

Using Business database mentioned before we can:
- Add a Business Information (Name, Address, Phone #, etc. )
- Retrieve Business information

Create web form using any HTML editor.
When <submit> is clicked, information is pushed into database. Actually, index2.php3 is called with the PHP script embedded in the HTML page.

index2.php3

No HTML is written, just a PHP script that handles the data. PHP automatically creates variables to match the text boxes on the previous pages. The names of these variables are derived from the names of the text boxes in the previous HTML form.

<?php
if($submitbz) { // If the submit button is pressed
$cnx = odbc_connect( 'bzrsc' , '' , '' ); // connect to odbc database
// insert query
$query = "insert into bzrsrc (bzname, address, phone_no, fax_no, email, website, contact_person, category_id) values('$bzname', '$address', $phone_no, $fax_no, '$email', '$website', '$contact_person', $category_id)";

$cur = odbc_exec( $cnx, $query ); //execute odbc query

if( $cur) print "Information entered into Database."; // if insert sucessful

else print "Error, cannot insert into database";

odbc_close( $cnx); // close the connection.

}

?>

Retrieving information from the database

We may want to see all businesses in the database
We create another HTML form
When <search> is clicked, information is pulled from database.

<?php

$query = "select * from bzrsrc where category_id = ".$category_id." order by bzname";

$cnx = odbc_connect( 'bzrsc' , '' , '' ); // connect to database

$cur= odbc_exec( $cnx, $query ); // execute query on database

print "<table border=0 width=60%>\n";
while( odbc_fetch_row( $cur ) ) //get the result rows

{

$bzname= odbc_result( $cur, 1 );

$bzid= odbc_result( $cur, 2 );

print "<tr><td><a href=\"info.php3?id=$bzid\">$bzname</a></td></tr>\n";

}print "</table>";

odbc_close( $cnx); // close connection

?>

Search Results

We will see the this result page on searchresults.php3

Alternatives

Of Course, there are easier ways, better "windows like" ways to connect databases to the web.
- Cold Fusion, is a leader in this area.
- MS FrontPage uses, IDC/HTX
Both make database connections transparent to designer.
MS FrontPage, not scalable, can’t handle enterprise-based websites.
Win9x, Win NT, Win 2000, all come with a web server. PWS for Win 9X and IIS for Win NT and Win 2000. ASP is also available as a "plug-in" through the MS Options Pack

You still have to so some programming in VB Script, Visual Basic, etc. to enable dynamic web pages
Access 2000 makes it easy to develop solutions for small scale websites or intranets.
View, Edit, and Analyze Data in a web browser (using Data Access Pages). i.e. drag and drop Access components to HTML page.
Also create Static HTML or server-generated HTML files.