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.
Register
I’m running Windows NT 4.0 Workstation on an NT 4.0 Server-based network and want to use the NT Workstation as a Web server for my small department intranet. The workstation has Peer Web Services (PWS) running with Service Pack 3 (SP3) per the article. Hook does not specify where to put the dmc.mdb file. If I put it in the C:\inetpub\scripts directory on the workstation where the YearQuery_1.xxx files are, I can get everything to work exactly the way the article showed. However, I’d rather have the dmc.mdb file on our network server, which gets backed up more frequently. If I set the dmc.mdb database in a directory on the network server and reestablish the Open Database Connectivity (ODBC) requirement to point to that file and run the example, I get the following error: >[State=S1000][Error=-1032] [Microsoft][ODBC Microsoft Access 97 Driver] The Microsoft Jet database engine cannot open the file ‘(unknown)’. It is already opened exclusively by another user, or you need permission to view its data.
I checked permissions on the dmc.mdb file on the server, and I should have access to it. The file is not open. I get the same error when I log on as a domain administrator. Any ideas?<br>
--Chuck Lob<br><br>
<i>Try this fix. On the server, place the file in a shared directory that you can access from the workstation. On the domain, create an account with access to the file on the server and to the workstation. Replace the account that Peer Web Services uses with the domain account. Create a new datasource that points to the file on the server. Specify the location to the database as \\machinenamesharename\etc. If you don’t follow this step, you need a permanent connection to the shared drive as a network drive.
By the way, this fix works only if you haven’t installed the NT 4.0 Option Pack. (For an overview of the Option Pack, see Ken Spencer, “The NT 4.0 Option Pack,” January 1998.) If anyone knows a solution with the Option Pack installed, send it our way.<br>
--Simon Hook</i>
Chuck Lob August 10, 1999