What is a database?

A database is used to hold information (usually a lot of it!) and to allow the information to be sorted or filtered in many different ways. It's possible to use Excel as a database but the most popular database programs are Microsoft Access, Microsoft SQL Server and Oracle. Access is not the most powerful database but it is the most popular because it is easy to learn and well-integrated with the Microsoft Office suite.

Databases keep their information in tables made of rows and columns but (except in Excel) a row is usually called a record and a column is usually called a field. A record usually corresponds to some kind of object (such as a person) and the fields usually are properties of the objects (such as name and address, if the objects are people)

FrontPage allows you create an online Access database even if you do not own the Access program. Once you have an online database you can set it up so that it is possible using nothing more than a browser to

I hope you appreciate how significant this is - this is really the only way that a visitor to your site, using just a browser, can not only add information to your web (this can be done with a form without using a database) but can also modify or delete it. 

This tutorial explains how you can use FrontPage 2000 to create and work with an Access database. The tutorial is a modified version of a version available for free download from the Microsoft website.

It is also possible to use a database which has been created not by FrontPage but by Microsoft Access itself. The database must be imported into your web and then a connection must be set up - follow THIS LINK for help if you want to use an imported database.

Create a Database and a connection using an HTML Form

In FrontPage 2000, you can automatically create an Access database in your FrontPage web by correctly configuring a form that you have added to one of your pages.

1. Open a web or subweb on your database compatible (Windows-based) server, make a new blank page and save it immediately with the name mydatabase.asp.
   
   
2. With the insertion point in front of the Submit button, type "First Name: " (without the quotation marks). On the Insert menu, point to Form, and click One-Line Text Box. This will add  a one-line text bow, a submit button and a reset button all contained within a form, represented by a dashed rectangle.
   
   
   
   Right-click the text box, and click Form Field Properties. In the Name box, type "FirstName" (without the quotation marks), and click OK.
3. Repeat step 2 until your form looks similar to the following:

First Name:     Email Address:

Last Name:     Phone Number:

Use the following names for the form fields: FirstName, LastName, Email, Phone.

Hint: I believe that giving a form field the name "Name" may cause problems later on since a database field would then also be called "Name" but this is a reserved word in SQL (see later) so avoid giving any form field the name "Name".

Create the Database Using the HTML Form

To create the Access database with your HTML form, follow these steps:

1. Right-click anywhere in the form, and click Form Properties.
2. In the Form Properties dialog box, click to select the Send To Database option, and click Options.
   
3. In the Options for Saving Results to Database dialog box, click Create Database.
   
   
   
   You will receive the following message:
   
   
   
   Click OK. Click OK until you return to the form. Save the page.

FrontPage has created a database called mydatabase.mdb and a connection to the database called mydatabase. These names are based on the filename of the page. The database that we have created contains a single table called "Results" if you used the English version of FrontPage 2000 or "Résultats" if you you used the French version. Remember the three names that your database uses (database name, connection name and table name) since you may have to modify the instructions in these lessons in order to make them match the names that you are using.

If you want to check that your database really exists you can use the folder list panel (liste des dossiers) to find it - you will see that FrontPage has created a special folder called fpdb to contain the database. Don't try to open the database by double-clicking it unless MS Access is installed on your computer.

You'll also notice that FrontPage has created a file called global.asa - I'll tell you more about that later.

Notice that the database and its connection were created just by saving this page with its form - no one has visited this page yet or entered any information into the text boxes. You should open the page now in a browser (use the button) and use it to add a few records to the database.

More recent versions of FrontPage can create Access databases in more elegant ways and with more control but it's nice to know that it can be done in FrontPage 2000 also.

Retrieve and display information

With the following example, you will create a page that displays the first name and last name of each employee in the database. To do this, follow these steps:

1. In Folders view, create a new page called "fpdisplayall.asp" (without the quotation marks).
2. Open fpdisplayall.asp in page view.
3. On the Insert menu, point to Database, and click Results.
   
   
4. In Step 1 of the Database Results Wizard, click to select the Use an existing database connection button. Click to select mydatabase from the list.
   
   
   
   Click Next.
5. In Step 2 of the Database Results Wizard, make sure the Record source option is selected and Results is selected from the list. Click Next.
   
   
6. In Step 3 of the Database Results Wizard, click Edit List.
   
   
7. In the Displayed Fields dialog box, click to select all of the items in the Displayed fields list except ID, FirstName, LastName, Title, City, State, Email and Phone (the window will NOT look exactly like the one below). Click Remove.
   
   
   
   Click OK.
8. In Step 4 of the Database Results Wizard, click Next.
9. In Step 5 of the Database Results Wizard, click the Display all records together button, and click Finish.
   
   
10. Save fpdisplayall.asp and then test it.

Other Advanced Database Examples

You have already learned how to view records and how to add records to your online database (the technique was slightly different according to whether the database was created by FrontPage or imported from outside).

You can perform other operations such as modifying or deleting records by running custom queries using SQL (Structured Query Language) with the help of the Database Results Wizard.

Update a Record

In this example, you need to create the OldName and NewName fields. The query that is used will change the last name of an employee based on these two fields.

Create Your Form

1. Create a new page, and name it "fpfrmupdate.htm" (without the quotation marks).
   
2. Create a form similar to the following:
   
   Old Email:     
   
   New Email:     
   
   
   
   Name the text boxes OldEmail and NewEmail, respectively, by right-clicking each text box and clicking Form Field Properties.
3. Right-click your form, and click Form Properties.
4. Click to select the Send to Other option, and click Options.
5. In the Action box, type "fpupdate.asp" (without the quotation marks). You will create this page soon.
6. Save the page with the form.

Create the Page to Update a Record

In this example you will use SQL to update the email address an employee. To do this, follow these steps:

1. Create a new page, and name it fpupdate.asp.
2. On the Insert menu, point to Database, and click Results.
3. In Step 1 of the Database Results Wizard, click to select the Use an existing database connection, and click to select mydatabase from the list. Click Next.
4. Click to select the Custom Query option, and click Edit.
5. In the SQL Statement box, type the following SQL (Structured Query Language) statement:
   
   UPDATE DISTINCTROW
   Results 
   SET Email='::NewEmail::'
   WHERE Email='::OldEmail::' 
6. Click OK. Click Next until you can click Finish, then click Finish.

Add New Record

Create Your Form

1. Create a new page, and name it "fpfrmadd.htm" (without the quotation marks).
2. Create a form similar to the following :

First Name:     Email Address:

Last Name:     Phone Number:

Set the names of the text boxes to FirstName, LastName, Email, Phone.

3. Right-click your form, and click Form Properties.
4. Click to select the Send to Other option, and click Options.
5. In the Action box, type "fpadd.asp" (without the quotation marks). You will create this page soon.
6. Save the page with the form.

Create the Page to Add New Record

To make this form add a new record to your database, follow these steps:

1. Create a new page, and name it fpadd.asp.
2. On the Insert menu, point to Database, and click Results.
3. In Step 1 of the Database Results Wizard, click to select the Use an Existing Database Connection, and click to select mydatabase from the list. Click Next.
4. Click to select the Custom Query option, and click Edit.
5. In the SQL Statement box, type the following SQL:
   
           INSERT INTO Results (LastName, FirstName, Email, Phone)     
           VALUES ('::LastName::', '::FirstName::', '::Email::', '::Phone::')
6. Click OK. Click Next until you can click Finish, then click Finish.

Delete a Record

In this example, you need only to create the FirstName and LastName fields. The SQL statement that is used will delete a 
record based on these two fields.

Create Your Form

1. Create a new page, and name it "fpfrmdelete.htm" (without the quotation marks).
2. Create a form similar to the following.
   
    First Name:     
   
   Last Name:     

Modify Form to Delete a Record

To make this form delete a specific record, follow these steps:

1. Right-click your form, and click Form Properties.
2. Click to select the Send to Other option, and click Options.
3. In the Action box, type "fpdelete.asp" (without the quotation marks). You will create this page in the next few steps.
4. Save the page with the form.
5. Create a new page, and name it fpdelete.asp.
6. On the Insert menu, point to Database, and click Results.
7. In Step 1 of the Database Results Wizard, click to select the Use an existing database connection, and click to select mydatabase from the list. Click Next.
8. Click to select the Custom Query option, and click Edit.
9. In the SQL Statement box, type the following SQL statement:
   
          DELETE DISTINCTROW FirstName
           FROM Results
           WHERE (FirstName='::FirstName::')
           AND (LastName='::LastName::')
10. Click OK. Click Next until you can click Finish, then click Finish.