Use Microsoft Access 97 and Peer Web Services to Create Searchable Databases

Sly Slick, the president of the Dodgy Motor Company (DMC), wants DMC to go high tech. When Sly was surfing the Web recently, he noticed that many Web pages had database searches to help viewers find information quickly. So he has asked you to set up a Web page with a database search of DMC's used car inventory. Sly wants car buyers to be able to search his inventory by a car's make, model, and model year.

For the database search, you can use a Web form that searches a Web server database. You can use Microsoft Access 97 and the Peer Web Services built into Windows NT 4.0 Workstation to create a dynamic Web page that contains the Web form and searches the database.

Screen 1, page 164, shows an example of a Web form that you can create. This form searches a database of DMC's used cars. The Web form has three fields: two text fields and a numeric field for the make, model, and model year, respectively.

If you leave all the fields blank and click on Run Query, you get a table that lists all the used cars in the DMC inventory, as shown in Screen 2, page 164. For each of DMC's four cars, the table includes the make, model, model year, and a hyperlink. If you click on the hyperlink, you get a picture of the car.

To narrow the search, you can type Hon in the [Enter Make] field and click on Run Query. This time only the two Hondas are in the table.

You can narrow the search even further by typing in 1994 in the [Enter Year] field and Hon in the [Enter Make] field. In this search, only the Honda Accord appears in the table.

You just conducted three types of searches. The first search left all the fields blank. The second search used a partial match of a text field in the database. The third search used a partial text match and a full integer match. In addition, you returned a hyperlink to another file on the system, which in turn, pointed to a picture (although the file could have pointed to another HTML file).

Now that you know the types of searches possible with this form, I will show you how to set up the database, make the database accessible to the Web, and set up an integer search. Part 2 of this two-part series, which will be in an upcoming issue, will look at how to set up text searches and hyperlinks.

Setting Up Peer Web Services
From an account with Administrator privileges, go to the Network applet (in Control Panel), select the Services tab, and add the Microsoft Peer Web Services (PWS). When NT prompts you to install the Open Database Connectivity (ODBC) drivers, select SQL Server. If you are installing PWS on your C: drive, the default directory for the program will be c:\WINNT\system32\inetsrv. If you install PWS in a different directory, make sure the directory name does not include a space (e.g., c:\Program Files). Otherwise, the shortcuts in the Start menu might not function correctly.

After installing PWS, go to the Microsoft Peer Web Services group in the Start menu and select the Internet Service Manager (ISM) icon to see what services are available. Three services appear: a WWW service, a Gopher service, and an FTP service. Select the WWW service, and then double-click on it. You will get a list of WWW service properties arranged in a tabbed dialog.

The Service tab lists the name of the account used for anonymous logon when someone accesses your site. If you have an NTFS-formatted disk, you can use NT's built-in security to limit access to your site. But if the people accessing your site will not be using Internet Explorer (IE), you must check the Password Authentication Basic (Clear Text) box. Be aware that if you select clear text, you will be using unencrypted passwords on the Internet.

The Directories tab lists the directories and the Default Document (i.e., what people see if they just type in your Internet address) that the WWW service uses. When you click on the Directories tab, you will see a scripts directory. Edit the properties of this directory to permit both execute and read access. You need both permissions to put the initial Web form in the scripts directory.

Installation of the WWW services creates a default homepage called default.html under the Inetpub\wwwroot directory. When you activate the WWW server, users can access Web pages on your server by typing the command

http://mycomputer

where mycomputer is your machine's name (including the Internet domain).

Setting Up the Database
Install Office 97 Professional, open Access 97, select Create a Blank Database, and name the database DMC.mdb. These steps will bring up a tabbed dialog with the Tables tab selected. Click on New to generate a new table, and select Datasheet View. Key in the data under the Make, Model, and Year columns of the table shown in Screen 3. (Do not key in the ID data or the column headings.) Make certain that the Make data appears under Field 1, the Model data appears under Field 2, and the Year data appears under Field 3.

Switch to the Design View by right clicking the title bar of the table. When Access 97 asks you to name the table, type Inventory. Access 97 will also ask whether you want a primary key. Say Yes. Next, change the names of Fields 1, 2, and 3 to Make, Model, and Year. Note that the table doesn't include the hyperlink shown in Screen 2. You will add the hyperlink later. Save and then close the table.