Build a FAQ with ASP and MS Access

All things considered, ASP is a fairly easy technology to use. Compared to some of the other products out there that let you develop dynamic Web sites with server-side scripting, there is relatively little to daunt the ASP newbie. It's easy to set up, the commands are simple, and the concepts involved in developing a run-of-the-mill ASP Website are relatively simple to grasp. Nevertheless, it has always been frustratingly difficult for ASP beginners to get the information they need to build a database-driven Website using ASP unless they could afford to buy (and spend a month reading) a great, big book on the subject. With this article, I aim to change all that!

Although not ideal for Websites with more than a little traffic, Microsoft Access is a nice database for learning the basics of database-driven Web development with ASP. In this article, I'll demonstrate how to access, retrieve, and update information stored in an Access database by using ASP to build a simple Web database application. The example we shall study will be a Web-based Frequently Asked Questions (FAQ) list. Through this case study, I'll show not only the immense convenience that comes with using a database to build a Web app like this, but also a few nifty tricks that having a database backend makes possible.

Before we get up to our necks in code, let me tell you what I expect from you. This article will assume that you have an understanding of the basics of both ASP and Microsoft Access. If you're new to ASP, you're in luck; I've already written a great series of articles that will bring you up to speed in no time. Begin with Getting Started with ASP, then read ASP Language Basics, Handling Submitted Data with ASP, and finally ASP Sessions and Applications, then you'll be ready to tackle the material presented here. For those of you who have never worked work MS Access, a couple of good tutorials to get you up and running quickly are First Steps with Access and Relational databases, both by Helen Bradley. With those feathers securely in your cap, we're ready to get started. Let's go!

The FAQ Database

First thing's first. Before we build an ASP-based page to display the contents of a FAQ database, we need to build that database. For this application, we'll only need a simple one-table database. Open MS Access and create a new, blank database called faq.mdb. Create a single table called FAQ, with the columns shown here:

Once you've created the table, fill in a few entries so that you have something for your ASP scripts to work with:

Notice that the Views column should always be set to zero for new entries, to indicate that they have yet to be viewed. To make this easy, be sure to set that column to be Required with a Default value of 0. You can also change the Question column from Text to the Memo data type if you find you need questions longer than the 255 character limit imposed by Text (Memo allows up to 65,535).

That's all there really is to the database behind our simple FAQ system! If you plan to update the database often, you'll probably want to set up a form in Access for editing your FAQs, but that is beyond the scope of this article, and there's plenty of information in the MS Access help file on how to create forms.

To put your database online, you need to copy the faq.mdb file to your Web server (unless of course you're using your own computer as your ASP server), and put it someplace where your ASP scripts will be able to access it. Do not put the file in the same directory structure as your Web site's files, however, or visitors to your site will be able to download the entire database just as they would any other file on your site. In this example, it wouldn't matter much since we'll be making all of the database's contents visible through ASP, but if you used a database like this to store usernames and passwords, or private information about visitors to your site (e.g. names, email addresses, etc.), you would not want your database file to be downloadable! If you need help deciding where to put your database file on your server, ask your Web host for advice.

Now that you've created your database and placed it online, you're ready to write some ASP scripts to use it!