PART 2: Sly Slick and his Dodgy Motor Company's cars are cruising the Internet highway
If you read Part 1 of this series in the December 1997 issue, you've made headway in your effort to help Sly Slick, the president of the Dodgy Motor Company (DMC), get his used cars on the Internet highway. So far, you've installed and configured the Microsoft Access 97 database for DMC and set up Windows NT Workstation 4.0's Peer Web Services. You've also set up a simple integer parameter query to sort the DMC database, which contains information on Slick's used car inventory. In the process, you created an HTML file (the Web form), an Internet Database Connector (IDC) file (which searches the database), and an HTML Extension (HTX) file (which formats the end result). But your work is far from over. Using these three files, you are now ready to set up text searches and add hyperlinks to your dynamic results table.
Building a String Parameter Query
To start, you need to build a string parameter query. Open Access 97 and the DMC database you created previously. 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 Add to add the highlighted Inventory Table. Close the Show Table dialog box.
In the Inventory Table, double-click three areas: Make, Model, and Year. Then enter [Enter Make] in the Criteria field under Make. The resulting Select Query will look like the query in Screen 1, page 194. Close Select Query, saving it as MakeQuery.
With MakeQuery highlighted, click Open. An Enter Parameter Value dialog box will appear, prompting you to enter a value. Enter Datsun, and click OK. A table that lists the Datsun Hatchback in the DMC inventory will appear. Close the table.
Publishing String Parameter Queries to the Web
Now you need to publish your string parameter queries to the Web. With the database still open, select File|Save as HTML to invoke the Publish to the Web Wizard. Click Next to move past the first screen, which will be completely gray. Select the Queries tab, MakeQuery, and Next. Access 97 will prompt you for a template. Leave the field blank, and click Next. Access 97 will ask you to choose how to publish the data. Select Dynamic HTX/IDC (Internet Information Server—IIS), and click Next. When Access 97 prompts you for a Data Source Name, enter DMC and click Next. (You set up the DMC database as an Open Database Connectivity—ODBC—data source in Part 1.) Access 97 will then ask you where you want to publish. Enter c:\Inetpub\scripts if you are using the Peer Web Services defaults. (If not, enter the appropriate address.) Click Next. Finally, Access 97 will ask whether you want to create a home page. Leave the check box empty, and click Finish. This step will bring up an Enter Parameter Value dialog box that is identical to the one you used to build the parameter query. Leave the field blank, and click OK. As before, this process creates three files in your scripts directory: MakeQuery_1.html, MakeQuery_1.idc, and MakeQuery_1.htx.
Next, open your Web browser and go to http://mycomputer/scripts/ MakeQuery_1.html, where mycomputer is your machine's name (including Internet Domain). If you get an Access Denied error message, reread the section on Setting Up the Peer Web Services in Part 1 of this article. If you see the view in Screen 2, enter Datsun and click Run Query. You will receive an error message stating, Too few parameters. Expected 1.
Open MakeQuery in Design View in Access 97. Highlight the Make column in the query grid, and select Query|Parameters from the menu bar. A Query Parameters dialog box will pop up. As Screen 3 shows, you enter [Enter Make] in the Parameter field and Text in the Data Type field. Click OK to close the dialog box, and then close MakeQuery. When Access 97 asks you whether to save the changes, click Yes.
Select File|Save as HTML, and follow the same steps to save the MakeQuery to the scripts directory. Open the new MakeQuery_1.idc in a text editor, such as Notepad. You will see the listing in Screen 4, page 195. Note the single quotes around %[Enter Make]%. These quotes mean that the value you substituted for [Enter Make] will be treated as text.
Rerun http://mycomputer/scripts/MakeQuery_1.html from your browser. This time, you will have a working Web-based text parameter query. However, the query isn't the same as the desired query. Two problems exist. First, if you leave the field blank and click Run Query, the database won't return any data. (It should return all the data.) Second, if you enter a partial text match (such as Dat) in the Make box and click Run Query, you won't get any data. (It should return the data on the Datsun.)
To fix these two problems in Access 97, you need a Like & OR Is Null statement. Open MakeQuery in Design View. Replace [Enter Make] with
Like [Enter Make] & "*" OR Is Null
on the Criteria line. This new statement tells the database to append a wildcard to the letters entered and find all the matches for those entered letters or return everything if the field is blank. Close and save MakeQuery. Then, select MakeQuery, click Open, and enter Dat. You will get a table listing the values for the Datsun entry.
Although selecting File|Save as HTML and save new HTML, IDC, and HTX files is the next logical step, these files won't work because the IDC SQL statements use different wildcard characters and behave differently from Access 97 SQL statements. When Access exports a query that contains a parameter concatenated with an asterisk, the SQL statement that Access generates contains a parameter concatenated with a percent sign. The IDC SQL statement does not need the concatenation operator. Instead, it needs a series of percent signs placed after the parameter. (The Microsoft article Q163893, "ACC97: IDC Parameter Queries Cannot Use LIKE and Wildcards," contains more information. Go to http://premium.microsoft.com/ support/kb/articles/q163/8/93.asp.)
So, instead of saving the query to HTML, simply edit the code in the existing IDC file with Notepad until it matches the code shown in Screen 5 and save the updated file. Replacing the code solves the partial match problem because the IDC appends a wildcard to partial matches. Replacing the code also solves the null query problems because if the field is left blank, the IDC submits a wildcard as the query.
If you compare the code in Screen 5 to Microsoft's example code, you notice that the MakeQuery code in Screen 5 contains additional percent signs. These extra percent signs let the database return information on the Datsun if a user enters just "sun." This feature is useful when users query on the year because they can enter 82 and get information on the 1982 car.
Now that the text parameter query works for Make, you can create the text parameter query for Model. You have two approaches to choose from. In one approach, you can edit the IDC file and add a similar line for Model using an AND statement to query on both Make and Model. You then need to edit the HTML file to add an edit box for Model to your Web page. In the other approach, you can create a new query that includes the Make, Model, and Year fields in Access 97. You need to put [Enter Make] and [Enter Model] on the criteria line for Make and Model, respectively, and then save the query as HTML. You still have to correct the new IDC file, but the HTML file will be okay.
For the DMC database, you decide to use the new query approach, saving the new query as DMCQuery. Screen 6 show the final IDC file; Screen 7 contains the final HTML file. At this point, you might want to move the HTML file into the \wwwroot directory and change the location of the IDC file specified in the HTML file. These modifications will let you change the permissions on the scripts directory back to execute rather than read and execute.