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.
Adding the Integer Parameter Query
In Part 1 of this series, you set up a simple integer parameter query. This query suffers from the same problems as the early string parameter queries: If you leave the query field blank or if you enter in a partial number match, the database won't return any data. You can use one of four solutions to fix these problems:
- Use a scripting language to determine whether the completed Web form contains an integer value.
- Add a line in the IDC file to signal that the field must be completed (e.g., RequiredParameters: \[Enter Year\]).
- Change the field from integer to text because you are looking only for exact or partial matches and not doing any mathematical calculations or logic.
- Use a format statement in the IDC file to format the integer output as text so you can use a Like statement.
The fourth approach is a good choice because you can still treat the field correctly when developing any queries within Access 97. To use this approach, open Access 97 and then open YearQuery in Design View. Delete the Criteria line for Year. Create a new column in the query grid, as Screen 8 shows. In the Field line, put the statement: Format(\[Year\],"#.#"). In the Criteria line, put the Like statement: Like\[EnterYear\]&"*". Note that you have not selected the show check box for the column. Close and save the updated query.
With YearQuery highlighted, click Open. An Enter Parameter Value dialog box will appear, prompting you to enter a value. Enter a partial value (such as 199), and click OK. You will get a table that lists the Ford Taurus and Honda Accord. Of course, if you save the query as HTML with Access 97, it will not work for the reasons discussed previously. However, you can apply the same fixes and update the HTML and IDC files to make the YearQuery work. Screens 9 and 10 contain updated listings for the IDC and HTML files, respectively.
Adding a Hyperlink Field
Access 97 includes the option of adding hyperlinks to tables using the hyperlink data type. This new data type has three parts—the display text, address, and subaddress—which Access 97 separates with hash marks (#). Each hyperlink must have an address, subaddress, or both. The address references a universal resource locator (URL) or a file. The subaddress points to something in a file. The display text is what the user sees in the table. If the display text is missing, Access 97 displays the address and subaddress instead.
Before adding hyperlinks to the Inventory Table, however, you need to create an archive directory that will store the .jpg files of the cars (e.g., under InetPub). Once you have created this directory and added the necessary .jpg files, you need to use the Internet Service Manager (ISM) to add the archive directory to the list of directories. You can specify the alias for the directory as /archive and give read access to anonymous Internet users.
Now you can add the hyperlinks. Select the Inventory Table in the DMC database and go into the design mode. On the first blank line in the Field Name column, enter HomePage. Right-click the Data Type column. From the drop-down list, select Hyperlink. Close and save the table.
Select Inventory Table and click Open. An additional empty HomePage column will appear. Right-click the first cell in the HomePage column, highlight the hyperlink, and click Edit Hyperlink. Enter the car's .jpg address and leave all the other fields blank, as Screen 11, page 197, shows. (In Screen 11, the path is http://shook2-ppp/archive/ datsun.jpg. On your screen, the full Internet address of your machine will be where shook2-ppp is.) Click OK. The Inventory Table will display the full path to the hyperlink.
Next, right-click the new hyperlink, and highlight Hyperlink on the shortcut menu. In the Display Text edit box that appears, enter View Datsun and press Enter. The hyperlink will now be labeled as View Datsun in the table. Repeat this procedure to add the hyperlinks to the pictures of the other cars in the database. Close the table.
Click the Queries tab, open a New query in Design View, and add the Inventory Table. Double-click Make, Model, and Year to add them to the query. (Each will have a check mark in the Show box.) You should be able to double-click HomePage to have the hyperlink returned when you save the query as HTML, but another problem exists: When you view an IDC file's hyperlink fields in a Web browser, they appear as text with hash marks.
Microsoft has a macro, as Screen 12 shows, to solve this problem. However, do not try to copy this macro from Screen 12. Instead, download it from http://premium.microsoft.com/ support/kb/articles/q163/6/54.asp.
After you download the macro, add it to the field line in the final column in Select Query. Remove the underscores at the end of each line. (Microsoft uses underscores as a line continuation character.) You can also change GoodHyper: to Plots: to customize the macro to your application. The macro in Screen 12 has this optional customization.
Close the query, and save it as HyperQuery. Save the HyperQuery as HTML, and put the output in the scripts directory. This time, you will get only IDC and HTX files because you never set up a parameter query. That's okay because the query wouldn't have worked because of the problems outlined previously.
Open theDMCQuery_1.idc file and the HyperQuery.idc file. On the SQLStatement line in the HyperQuery.idc file, you will see '<A HREF at the beginning of the macro. Next, add the macro in the SQLStatement: line in the HyperQuery.idc file (starting with '<A HREF) to the end of the SQLStatement: line in the DMCQuery_1.idc file. (Don't forget the comma after Inventory Year.) Save the DMCQuery_1.idc file. Screen 13 shows the listing (the end of the macro is truncated). Next open HyperQuery_1.htx and replace all occurrences of HyperQuery with DMCQuery, and save the file. Then rename the file DMCQuery_1.htx.
Cruising the Internet Highway
Now, the time has come to see whether your efforts have been successful. Access the Web form. Leave all the fields blank, and click Run Query. If you get a table that lists all the used cars in the DMC inventory, as shown in Screen 14, congratulations! You have successfully developed a searchable database containing DMC's used cars.
Because of feature differences between the Peer Web Services and Access 97, the project was a bit tricky at times. But you persevered. As a result, you have a useful Internet application, and Sly Slick and his cars are cruising the Internet highway.
Although Active Server Pages (ASP) and IIS 3.0 have now superseded IDC, IDC is still useful. If you want to learn more about IDC, see Stephen Wynkoop's article, "Maintaining IDC or RDS Legacy Apps without Agita," Microsoft Interactive Developer, November 1997. An online version of the article is available at http://www.microsoft.com/mind.
