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.