Use Microsoft Access 97 and Peer Web Services to Create Searchable Databases - 01 Dec 1997

PART 1: Help Sly Slick get Dodgy Motor Company's cars on the Internet highway

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. Thisform searches a database of DMC's used cars. The Web form has three fields: twotext 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 tablethat lists all the used cars in the DMC inventory, as shown in Screen 2, page164. For each of DMC's four cars, the table includes the make, model, modelyear, and a hyperlink. If you click on the hyperlink, you get a picture of thecar.

To narrow the search, you can type Hon in the [Enter Make] field and clickon 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 Accordappears in the table.

You just conducted three types of searches. The first search left all thefields blank. The second search used a partial match of a text field in thedatabase. 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 inturn, pointed to a picture (although the file could have pointed to another HTMLfile).

Now that you know the types of searches possible with this form, I will showyou how to set up the database, make the database accessible to the Web, and setup an integer search. Part 2 of this two-part series, which will be in anupcoming 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 (inControl 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, thedefault directory for the program will be c:WINNTsystem32inetsrv. If youinstall PWS in a different directory, make sure the directory name does notinclude a space (e.g., c:Program Files). Otherwise, the shortcuts in the Startmenu might not function correctly.

After installing PWS, go to the Microsoft Peer Web Services group in theStart menu and select the Internet Service Manager (ISM) icon to see whatservices 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. Youwill 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 whensomeone accesses your site. If you have an NTFS-formatted disk, you can use NT'sbuilt-in security to limit access to your site. But if the people accessing yoursite will not be using Internet Explorer (IE), you must check the PasswordAuthentication 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 serviceuses. When you click on the Directories tab, you will see a scripts directory.Edit the properties of this directory to permit both execute and readaccess. You need both permissions to put the initial Web form in the scriptsdirectory.

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

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

Setting Up the Database
Install Office 97 Professional, open Access 97, select Create a BlankDatabase, and name the database DMC.mdb. These steps will bring up a tabbeddialog with the Tables tab selected. Click on New to generate a new table, andselect Datasheet View. Key in the data under the Make, Model, and Year columnsof the table shown in Screen 3. (Do not key in the ID data or the columnheadings.) Make certain that the Make data appears under Field 1, the Model dataappears 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 alsoask 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 thehyperlink shown in Screen 2. You will add the hyperlink later. Save and thenclose the table.

Building an Integer Parameter Query inAccess 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 ShowTable, 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. Inthe Criteria field for Year, add [Enter Year]. At this point, the Select Querywill appear as shown in Screen 4. When you close Select Query, Access 97 willprompt you to save the query. Save it as YearQuery.

Next, with the YearQuery highlighted, click on open. Access 97 will promptyou 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 inthe database. Close the table and Access 97.

Installing Service Pack 3
Perform a full backup (including the Registry), and update your EmergencyRepair Disk. Then install Service Pack (SP) 3, which includes the latest versionof the ODBC API. This latest version features an updated ODBC Control Panel andan ODBC Administrator interface that uses tabbed controls and provides moreinformation 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 a32-bit ODBC data source that points to the database file. The ODBC API defines adata source as a specific combination of the data a user wants to access, thedata's associated database management system (DBMS), the platform on which theDBMS resides, and the network (if any) used to access the platform. ODBCprovides a common interface for accessing heterogeneous SQL databases.

To create the 32-bit ODBC data source, open the ODBC Data SourceAdministrator in the Control Panel of NT 4.0. Select the System DSN tab. Thistab lists all the system data sources that are local to a computer rather thandedicated to a user.

With the System DSN tab highlighted, click on Add. NT 4.0 will prompt youto 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. Inthe Data Source Name field, enter DMC. Then click on Select, and pick theDMC.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 AsHTML to invoke the Publish to the Web Wizard. The wizard will give you a list ofhow you can publish your data. Click on Next. In the tabbed dialog box, selectthe Queries tab and check YearQuery. (You created this query to search theInventory Table by year.) Select Next, which will prompt you to provide atemplate. Leave the field blank, and click on Next.

When Access 97 asks how you want to publish the data, select DynamicHTX/IDC (Internet Information Server) and click on Next. Now you will need toprovide a Data Source Name. Enter DMC (which is the database you made accessiblevia ODBC), and click on Next. Access 97 will ask where you want to publish thedata. If you used the defaults when you installed the Peer Web Services, enterc:Inetpubscripts and click on next. (If you decided to publish the files in adifferent directory, make sure you set the permissions on that directory to readand execute.)

When Access 97 asks whether you want to create a home page, leave the checkbox empty and click on Finish. An Enter Parameter Value dialog box identical tothe one you used when you built your parameter query will pop up. Leave thefield blank and click on OK. Close Access 97. If you leave the file open and runa 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 forthe directory are set at read and execute.

Type 1982 in the [Enter Year] field. You will get a table listing the datafor the Datsun, as shown in Screen 7. If you leave the field blank and click onRun Query, you will get an error. I will address this problem in the secondarticle of this series.

What You've Accomplished So Far
At this point, Access 97 has added three files to the scripts directory:

In the next article, I will not only discuss text searches andhyperlinks, but also look at customizing the IDC file. In the meantime, you cancustomize the look of the results table by editing the HTML in the HTX file orby opening the file in FrontPage 97.

To edit the HTX file with FrontPage 97, open the file. Select andright-click on the different parts of the table to get a list of the options youcan change.

To find out more about the other Web publishing features of Access97, check out Rick Dobson, "Publishing Databases on the Intranet UsingPersonal Web Server and Access 97," Microsoft Interactive Developer,April 1997. An online version of the article is available on Microsoft's Website at http://www.microsoft.com/mind.