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