Web Access to ACCESS and Other Databases

In this section of Untangling the Web, I'm going to talk about using the WEB to access databases, including Microsoft's ACCESS Database. This is really a simple way to do simple updates and to generate reports using Microsoft IDC - Internet Database Connector. There are lota of other ways to do this, and this is just an example of the way it might be done. To do it with IDC, it requires MS Access 97 to author, and Windows 95 or NT as an MS web server - but any web browser on any platform can use this technique. The web server has to be the personal web server for Windows 95, the peer web server for NT Workstation, or IIS for theWin NT server. Accessing databases form the WEB is something that you can do right now. You can do it today. There's two independent parts involved in accessing databases from the WEB. In fact, we're going to provide you with step-by-step instructions that will enable you to do this right now. One part is displaying an Access report on the web. And the other part is updating an Access file from the web. You can do either or both together. Both make a great deal of sense.

Now for A Real Example. This is a typical survey form on the web - except that it updates an ACCESS database as soon as the "SEND" button is pressed. A user needs only to know the URL of the form to use it from any browser. A user just fills in the name of the software product, selects an item from the "Category" pull down menu, selects the appropriate radio buttons, supplies a user id, and presses send. The value of all the data entered is immediately added to the Survey table of the Survey database and a success message is returned.

Let's take a look and see how this actually works. The Web Tools Survey is plain HTML that can be built any way you'd build HTML. The Web Tools Survey includes a web form that is filled out by users using any web browser. The filled out form is sent to a web server that can handle Microsoft's IDC database access system. Many other software vendors have and will have similar ways to do this. Using ODBC drivers that have been configured for the Survey database - this is much easier than it sounds - the web server uses SQL commands in a user-built IDC file - also simpler to do than it sounds - to add the information from the web form to the Survey database. Once the database has been updated - usually in a fraction of a second - the user defined HTX file, which is similar to an HTML file - is returned to the browser. In most simple cases, the HTX file will just contain a message telling the user that the database was successfully updated.

Here's 3 URL's that show you the result in 3 different ways. The first shows you a static report. A static report is a snap shot of the database and doesn't change. As we update the database, the snapshot remains the same. And this is very useful if that's the kind of thing we want to look at. The next is a dynamic unmodified report. As we add results to the database, this dynamic report changes, however, what we've done is we've taken the entire table and we've not modified it in any way. It hasn't been sorted. We haven't deleted and fields or done anything like that. The last example shows a dynamic report that has been sorted, and one of the fields ahve been deleted. These are just three of the many examples of how we can display the results of updating an ACCESS database on the WEB.

Now, here's three step-by-step guides telling you how to use ACCESS with the WEB. The first tells you how to publish a static report. THe next tells you how to publish a dynamic report. And the last tell you how to insert records in an ACCESS database for the WEB.

Now, let's take a look at the actual IDC file and build a survery database that we just took a look at. Some software packages such as MS FrontPage or MS Access will build an IDC file for you, but there are many times - at least today - when you'll have to build it yourself. The IDC file shown is for the Survey database and is typical of what an IDC file will look like when you are adding records to an ACCESS, ORACLE, or other database. The DATASOURCE entry is the name of the ODBC entry. Make it the name of your database as well to keep things simple. The TEMPLATE is the name of the HTX file returned. Using your database name here as well is also helpful. The SQLStatement: lines can contain nearly any SQL statements. For the common case of inserting a record from a web form - as you would do for a survey, a registration form, or an exam on-line - the line consists of the command "INSERT INTO", followed by the name of the database table into which the data should go. Then, within parens, there is a list of all the fields from the web form. These should have the same names as the field names in your database table. Your web form does not have to include every field in a database table. Following this list is the command "Values" followed by a list of the field names within parens again. This time each field name is formatted as shown - enclosed in percent signs and then within quotes. For example '%Tools%' . This tells ACCESS that all fields are TEXT fields. Until you are more experienced, use only text fields. There are interesting problems that occur when a web form fails to provide a value to a field that is not a text field.

And here's the HTML for the opening FORM tag for the form used to access the survey database. The only special thing in the HTML of a form that updates a database is the ACTION parameter of the FORM tag. The parameter must reference the IDC file that contains the SQL statements that update the database. Notice that this file has an extension of IDC.

Here's an example of accessing an ACCESS database for the WEB. This is a registration form used to register people for a JAVA Day Seminar at Princeton University.

Normally you'd have to store at least the .idc, .htx, and .mdb files on the same computer as the web server. This creates an administrative headache and potential security problems for the server administrators. It is also a nuisance for users who must seek the assistance of the server administrator every time they want to add or change any of the .idc, .htx, or .mdb files. A better way to do this is to have the IIS server mount a user file system. This file system should be accessible by all users who are authorized to build web pages that can access databases. That should include at least all faculty and staff, and possibly all students as well. In this example, the user file system is on a Novell server. Every user views the Novell file system as just another disk they have access to. And the Novell file system is equally easy to access from Macs, PCs, and UNIX computers. When a browser asks the IIS web server for a URL that references a file on the Novell file system, the IIS web server accesses the files it needs on the Novell server. All the server components, such as the ODBC drivers and the JET database engine, remain on the server and are shared by all users accessing all databases and queries. To a user of any browser, it looks like the entire Novell file system is on the same computer as the web server itself. Once a user creates an idc, htx, or mdb file, it is saved in his or her own directory on the Novell file system. For a user, this is virtually the same as saving these files on their own hard disks. And for the server administrators, there is no involvement. Well, almost no involvement. The server administrator needs to know the path of the files that each user has stored on the Novell file system. The administrator also needs to make an ODBC entry on the server for each database. These requirements turn out to be helpful to everyone. A user must effectively register each database to the server administrator. This should be done via the web of course, so it will not be a major administrative burden. The registration requirement ensures that the server administrators know what their web servers are up to, and what user to call when something doesn't work right. For the user, publishing web pages that can access databases becomes just a little more complex than publishing any other web pages. Those other web pages should also reside on a Novell file system - or another user file system - and could be served by the same web server or by other servers as shown with the dashed lines.

About CREN

© CREN, 1999

Contact Us