The source code and the MS Access 2000 database for this example may be downloaded by clicking here.

Using queries with ASP and MS Access ....

Queries in MS Access provide a visual way of creating complex SQL statements. We can use these pre-constructed queries in Active Server Pages (ASP) without having to worry about writing and using long SQL statements.

We can write a query once, test it and then use it as many times as we like. Also, if the query needs to be changed such as a different sort order, the change will be reflected throughout the project.

The methods shown in this tutorial can also be used with SQL Server. In fact SQL Server can be easier to work with than MS Access queries because it implements Stored Procedures which can return values.

The following code illustrates how to use an MS Access Query called "qryStudentsOnCourse" in an MS Access database called "courses.mdb". The query accepts an input parameter which is the course ID - in this example it is hard coded a "C0450" - and then a record set returns a list of the courses for that input parameter.

<%@ Language=VBScript %>
<%Option explicit

Dim cmd, rs, connect
Const adCmdStoredProc = &H0004
Const adParamInput = &H0001
Const adVarChar = 200

Set cmd = Server.CreateObject ("ADODB.Command")
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
      Server.MapPath("courses.mdb") & ";Persist Security Info=False"
cmd.ActiveConnection = connect
cmd.CommandText = "qryStudentsOnCourse"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter &_
      ("@CourseID",adVarChar,adParamInput ,10,"C0450")
Set rs = Server.CreateObject ("ADODB.Recordset")
Set rs = cmd.Execute

do while not rs.EOF
Response.Write rs("firstName") & " " & rs("lastName") & "<br>"
rs.MoveNext
loop

rs.Close
Set rs = nothing
set cmd = nothing
%>

Code explanation

Set cmd = Server.CreateObject ("ADODB.Command")

This starts with creating a command object which provides us with the ability to execute our query.

connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
      Server.MapPath("courses.mdb") & ";Persist Security Info=False"

The connection string uses MapPath to get the correct drive mapping, enabling the script to be used wherever it is installed.

cmd.ActiveConnection = connect

The active connection defines the connection to our database.

cmd.CommandText = "qryStudentsOnCourse"

CommandText defines which command we are about to execute, which is our query.

cmd.CommandType = adCmdStoredProc

CommandType identifies the type of command being executed, which in this case is a stored procedure.

cmd.Parameters.Append cmd.CreateParameter &_
      ("@CourseID",adVarChar,adParamInput ,10,"C0450")

We must create a parameter object with the values we are using.

Set rs = Server.CreateObject("ADODB.Recordset")

In MS Access, we can only have input parameters with a query, so to get our information out we have to create a recordset. In SQL Server we are able to use output parameters which makes it much easier to extract results from a stored procedure.

Set rs = cmd.Execute
do while not rs.EOF
Response.Write rs("firstName") & " " & rs("lastName") & "<br>"
rs.MoveNext
loop

Now that we have created the recordset we can display the contents of the recordset

rs.Close
Set rs = nothing
set cmd = nothing

Finally, close and de-reference the objects.

The source code and the MS Access 2000 database for this example may be downloaded by clicking here.