databasedev.co.uk - database solutions and downloads for microsoft access

Using ADO on the Internet

ASP, VB Script and Microsoft Access

In this article we will be looking at how to use Microsoft Access on the web. Basically the article will show how to access, retrieve and ‘publish’ information that is stored in a Microsoft Access database using VB Script and ASP. Both these scripting languages are fully supported (and indeed created) by Microsoft and is perfectly suited for this kind of task.

To fully participate in this article, you will need Internet Information Service or IIS 5 (as it is commonly known) or greater. It is available to those who have Windows XP installed on their systems. And you will also need notepad, which comes with any version of the Microsoft Windows operating system. We will be doing this in three stages, first we create a database and then we will create the ASP page and finally we will use the web server to serve up the asp page for us. So let’s get cracking.

Startup MS Access and create a blank database save it as: cdcollection.mdb. Then create a new table with the following fields:

Field Data Type
artistID AutoNumber
name Text

Save the table as ‘artist’. The design view of the ‘artist’ table structure should look like this:

The Artist table
Fig 1. The Artist table in Design View

Then create another table with the following fields:

Field Data Type
cdID AutoNumber
title Text
price Number
artID Number

Save the table as ‘CD’. The design view of the ‘CD’ table structure should look like this:

The CD table in Design View
Fig 2. The CD table in Design View

Now, populate the tables with any artist name and CD or album information that you want. That’s it for the database section.

Open up Notepad and copy and paste the following text:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {font-size: xx-large}
-->
</style>
</head>
<body>
<table width="426" border="0">
<tr>
<td colspan="2">
<div align="center" class="style1">Artist Search Form </div>
</td>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<form method="post" action="searchform.asp">
<tr>
<td width="92"><strong>Artist Name:</strong></td>
<td width="318"><input name="artist" type="text" size="40" /></td>
</tr>
<tr>
<td><input type="hidden" name="sub" value="true"></td>
<td><input name="submit" type="submit" value="Search" /></td>
</form></tr>
</table>
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
If Request.Form("sub") = "true" Then
' Declare variables
Dim adoCon 'Holds the Database Connection Object
Dim rsSearch 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query to query the database
Dim artistname 'Form var
' Gather input from search form
artistname = Request("artist")
' Open a connection to cdCollection database
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& Server.MapPath("cdcollection.mdb")
'Create an ADO recordset object
Set rsSearch = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT DISTINCTROW * FROM artist
INNER JOIN CD ON artist.artistID=cd.artID WHERE artist.name= '"&artistname&"'"
'Open the recordset with the SQL query
rsSearch.Open strSQL, adoCon
'Write the HTML to display the current record in the recordset
Response.Write ("<b> Your Search returned the following results for _
"&artistname&": </b>")
Response.Write ("<br>")
Response.Write ("<br>")
Response.Write ("<table width='100%' border='1' cellpadding='0' cellspacing='0'>")
Response.Write (" <tr>")
Response.Write (" <td bgcolor='#CCCCCC'><strong>CD Title</strong></td>")
Response.Write (" <td bgcolor='#CCCCCC'><strong>CD Price</strong></td>")
Response.Write (" </tr>")
'Loop through the recordset
Do While not rsSearch.EOF
Response.Write ("<tr>")
Response.Write ("<td>"&rsSearch("title")&"</td>")
Response.Write ("<td>$"&rsSearch("price")&"</td>")
Response.Write ("</tr>")
'Move to the next record in the recordset
rsSearch.MoveNext
Loop
'Reset server objects
rsSearch.Close
Set rsSearch = Nothing
adoCon.close
Set adoCon = Nothing
End if 'submit test
%>
</body>
</html>

Now save the file as ‘searchform.asp’, without the quotes. It is very important that you save the file as .asp and not .asp.txt. The script will not execute if you save it with a .txt extension.

Saving the file with an ASP extension
Fig 3. Saving the file with an ASP extension

Also make sure to save the file in your web servers root directory i.e c:\inetpub\wwwroot etc. The location of your root directory might be different so check to make sure.

Lets look at the code:

The first part of the ASP script deals with the HTML form that will take user input. The form section is highlighted in red. The actual scripting code is heavily commented so that it is easy to understand what is going on. But I want to explain the part of the code that deals with connecting and accessing information that is stored in the database. First a connection to the database is opened by creating a connection object:

' Open a connection to cdCollection database
Set adoCon = Server.CreateObject("ADODB.Connection")

Then a DNS less connection is used to set an active connection:

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _ & Server.MapPath("cdcollection.mdb")

Then a recordset object is created, this object will be used to store the information that is retrieved from the database:

'Create an ADO recordset object
Set rsSearch = Server.CreateObject("ADODB.Recordset")

A SQL query is defined, the query is used to retrieve information from the CD and artist tables:

strSQL = "SELECT DISTINCTROW  * FROM artist

INNER JOIN CD ON artist.artistID=cd.artID WHERE artist.name= '"&artistname&"'"

The recordset object is then used to store the data that is retrieved from the cdcollection database:

'Open the recordset with the SQL query 
rsSearch.Open strSQL, adoCon

That, in nutshell, is how ASP, VB Script and MS Access work together to publish information over the internet.

Finally lets take a look at what all that code looks like when you run it. In this case, I am searching for CD’s recorded by an artist called ‘Stanley’:

Running the Search form

There you have it. Microsoft Access can also be used as a underlying storage engine for small websites. It supports ASP and various other Microsoft and Open source scripting languages just as well as other DBM’s.

The Author

Leidago !Noabeb is a computer programmer based in Namibia. He has worked with both opensource and Microsoft technologies for over seven years and specializes in writing communications software. He has made many contributions to various online websites dedicated to web development. He can be reached at: leidago [at] googlemail.com