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.
Building an Integer Parameter Query in
Access 97
After you close the table, select the Queries tab, New, and Design View.
These steps will bring up a Select Query to which you will add a table. In Show
Table, click on Add to add the highlighted Inventory Table. Close the Show Table dialog box.
In Inventory Table, double click on Make, then Model, and finally Year. In the Criteria field for Year, add \[Enter Year\]. At this point, the Select Query will appear as shown in Screen 4. When you close Select Query, Access 97 will prompt you to save the query. Save it as YearQuery.
Next, with the YearQuery highlighted, click on open. Access 97 will prompt you to enter a year in the Enter Parameter Value dialog box, as shown in Screen 5 . Enter 1982, and click on OK. You will get a table that lists the Datsun in the database. Close the table and Access 97.
Installing Service Pack 3
Perform a full backup (including the Registry), and update your Emergency
Repair Disk. Then install Service Pack (SP) 3, which includes the latest version
of the ODBC API. This latest version features an updated ODBC Control Panel and
an ODBC Administrator interface that uses tabbed controls and provides more
information about the ODBC components in your system.
Making the Database Accessible Through ODBC
Before you can process Access 97 databases over the Web, you must create a
32-bit ODBC data source that points to the database file. The ODBC API defines a
data source as a specific combination of the data a user wants to access, the
data's associated database management system (DBMS), the platform on which the
DBMS resides, and the network (if any) used to access the platform. ODBC
provides a common interface for accessing heterogeneous SQL databases.
To create the 32-bit ODBC data source, open the ODBC Data Source Administrator in the Control Panel of NT 4.0. Select the System DSN tab. This tab lists all the system data sources that are local to a computer rather than dedicated to a user.
With the System DSN tab highlighted, click on Add. NT 4.0 will prompt you to select a driver. Highlight the Microsoft Access Driver, and click on Finish. NT 4.0 will then prompt you to enter the information about your data source. In the Data Source Name field, enter DMC. Then click on Select, and pick the DMC.mdb file. Click OK, and exit from the ODBC Data Source Administrator.
Publishing the Integer Parameter Query as a Web Form
Open Access 97 and the DMC database you created earlier. Select File|Save As
HTML to invoke the Publish to the Web Wizard. The wizard will give you a list of
how you can publish your data. Click on Next. In the tabbed dialog box, select
the Queries tab and check YearQuery. (You created this query to search the
Inventory Table by year.) Select Next, which will prompt you to provide a
template. Leave the field blank, and click on Next.
When Access 97 asks how you want to publish the data, select Dynamic HTX/IDC (Internet Information Server) and click on Next. Now you will need to provide a Data Source Name. Enter DMC (which is the database you made accessible via ODBC), and click on Next. Access 97 will ask where you want to publish the data. If you used the defaults when you installed the Peer Web Services, enter c:\Inetpub\scripts and click on next. (If you decided to publish the files in a different directory, make sure you set the permissions on that directory to read and execute.)
When Access 97 asks whether you want to create a home page, leave the check box empty and click on Finish. An Enter Parameter Value dialog box identical to the one you used when you built your parameter query will pop up. Leave the field blank and click on OK. Close Access 97. If you leave the file open and run a search from the Web, you will get an error stating the file is already in use.
Open up your Web browser, and go to http://mycomputer/scripts/YearQuery_1.html. If you see the display shown in Screen 6, give yourself a pat on the back. If you get an error message saying access denied, make sure the permissions for the directory are set at read and execute.
Type 1982 in the \[Enter Year\] field. You will get a table listing the data for the Datsun, as shown in Screen 7. If you leave the field blank and click on Run Query, you will get an error. I will address this problem in the second article of this series.
What You've Accomplished So Far
At this point, Access 97 has added three files to the scripts directory:
- YearQuery_1.html. This HTML file contains the form that the browser uses to submit values to the Internet Database Connector (IDC) file.
- YearQuery_1.idc. This file includes three fields. The first field points to the ODBC data source (e.g., DMC). The second field points to the HTX file. The third field includes an SQL statement that defines how to use the parameter submitted via the form to search the database.
- YearQuery_1.htx. The HTX file describes how to display returned data in the browser.
In the next article, I will not only discuss text searches and hyperlinks, but also look at customizing the IDC file. In the meantime, you can customize the look of the results table by editing the HTML in the HTX file or by opening the file in FrontPage 97.
To edit the HTX file with FrontPage 97, open the file. Select and right-click on the different parts of the table to get a list of the options you can change.
To find out more about the other Web publishing features of Access 97, check out Rick Dobson, "Publishing Databases on the Intranet Using Personal Web Server and Access 97," Microsoft Interactive Developer, April 1997. An online version of the article is available on Microsoft's Web site at http://www.microsoft.com/mind.
