Build a FAQ with ASP and MS Access

Retrieving Access Data with ASP

ASP is a framework designed to combine the simplicity of scripting languages such as VBScript and the power of object libraries normally used by full-fledged Windows applications to build powerful, dynamic Web applications. The ActiveX Data Objects (ADO) library provides ASP with the functionality required to interact with most database servers, Microsoft Access being one of them.

In previous articles of this series, we have seen that ASP provides several objects (Request, Response, Server, Application, and Session) to accomplish the basic tasks required of dynamic Web pages, such as form processing and session tracking. The ADO library, which comes with all current ASP servers, adds to that selection of objects to provide support for database access functionality in ASP. Learning how to use databases with ASP basically means learning about the objects in the ADO library.

The first and most basic thing we need to be able to do if we want to make our FAQ database available on the Web is to retrieve the contents of the database for display. Whenever you retrieve database records with ADO, you need a Recordset. Recordset is one of those new objects that ADO provides, and our first task is to create one. ASP makes this quite simple:

Dim rsFAQ  
Set rsFAQ = Server.CreateObject("ADODB.Recordset")

The first line above creates a variable (rsFAQ) to store our new Recordset object. The second line creates a new Recordset using the Server object's CreateObject method, then sets the rsFAQ variable so that it refers to this new object. Server.CreateObject is the standard method to create any object supported by ASP or its libraries. In this case, we want to create an instance of the Recordset object in the ADO library. All ADO objects must be preceded by the ADODB prefix to let ASP know which library you are referring to, so the argument "ADODB.Recordset" simply indicates that we wish to create an ADO Recordset object. The Set keyword is necessary to assign an object reference to the rsFAQ variable, as opposed to assigning it a simple VBScript value.

Once you've created a Recordset, you can fill it with records from the database with its Open method. In this basic case, Open takes two parameters:

• the table name we want to fetch the records from, and
• the connection string for the database.

Now, the name of the table is simply "FAQ", the name we gave to the table in Access. The connection string is a slightly more complex matter. Since the ADO library is capable of connecting to a great many database servers and a number of other data sources, the string must tell our Recordset not only where to find the database (the path and file name) but also how to read the database, by giving the name of its database provider.

Jet OLE DB is the database provider for Microsoft Access, and as of this writing the current version is 4.0. To connect to an Access database, therefore, the connection string must specify the provider as Microsoft.Jet.OLEDB.4.0. Thus, if the database file (faq.mdb) is stored in D:\faq on your Web server, your connection string should be:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\faq\faq.mdb

To give you an idea, here's what a connection string for the more advanced MS SQL Server looks like:

Provider=SQLOLEDB; Data Source=servername; Initial Catalog=databasename;  
User Id=username; Password=password

With all this in mind, here is the complete code to fetch the contents of the FAQ table into an ADO Resultset:

Dim rsFAQ   ' A Resultset for our FAQ  
Dim strConn ' The database connection string  
 
rsFAQ = Server.CreateObject("ADODB.Recordset")  
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\faq\faq.mdb"  
 
rsFAQ.Open "FAQ", strConn

Since it's more that likely that you'll need to use the same connection string in several ASP pages on your site, it is common practice to place your connection string in an application variable in your global.asa file as follows (see ASP Sessions and Applications for more on application variables):

<SCRIPT LANGUAGE="VBScript" RUNAT="Server">  
 
Sub Application_OnStart()  
 
 Dim strConn  
 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\faq\faq.mdb"  
 Application("strConn") = strConn  
 
End Sub  
 
</SCRIPT>

The code to retrieve the contents of the FAQ table is thus greatly simplified:

Dim rsFAQ   ' A Resultset for our FAQ  
rsFAQ = Server.CreateObject("ADODB.Recordset")  
 
rsFAQ.Open "FAQ", Application("strConn")

Now that we've learned how to fetch information from an Access database into ASP, we need to learn how to use it.