Calling MS Access Parameterized Queries from ASP

Instead of passing a SQL query through your ASP code against Microsoft Access as you would normally do, you can use the Queries design interface to create them in Access and then call them from your ASP code. It makes things easier to edit and maintain, and the results are returned faster.

Published: Apr 30, 2002 | Last Edited: Aug 20, 2005
Tested with: ASP 3.0, MS Access 2000
Category: ASP
43,887 views

Introduction

OK, Microsoft's Access database may not be the fastest database for the web, but how many times have we all used it for projects? Typically we create an instance of the Recordset object in our code and then issue our query against it. There are many ways and examples on how to do this. No matter what method you use though, you will have to pass your SQL query to your recordset. This article will show how to move the queries from your ASP code to Access. That way you can edit and maintain them better, and they become faster too.

Example

What if we had a long query to pass to our recordset? Consider the following scenario:

1	<%
2	Dim objConn
3	Dim objRS
4	Dim strSQL
5	Dim numCategoryID
6	numCategoryID = Request.Form("CategoryID")
7	Set objConn = Server.CreateObject("ADODB.Connection")
8	objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/faqs.mdb")
9	Set objRS = Server.CreateObject("ADODB.Recordset")
10	strQuery = "SELECT faq_category.ID, " _
11	& "Count(faq_category.name) AS count, " _
12	& "faq_category.name, faq_category.description "_
13	& "FROM faq_category " _
14	& "INNER JOIN faq_faq ON faq_category.ID = faq_faq.category_ID " _
15	& "GROUP BY faq_category.ID, faq_category.name, " _
16	& "faq_category.description, faq_category.parentID " _
17	& "HAVING faq_category.parentID=" & numCategoryID _
18	& " ORDER BY faq_category.name;"
19	objRS.Open strQuery, objConn, 0, 1
20	'-- continue with writing your recordset out
21	...
22	%>

The code above shows a typical scenario of connecting to an MDB Access file and issuing a long SQL query against it. The code gets complex and ugly as the query gets longer. Maintaining this in your ASP code is not easy.

Access Queries

Using Access's nice design interface, you can easily create a query. First, select the Queries tab in Access:

Then create your query using either the Design View or the Wizard. When you are done and it tests out fine, save it. Assume you name it faq_qry_sub_categories. After you save it, you can then call it from ASP:

1	...
2	Set objConn = Server.CreateObject("ADODB.Connection")
3	objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/faqs.mdb")
4	Set objRS = Server.CreateObject("ADODB.Recordset")
5	strQuery = "faq_qry_sub_categories"
6	objRS.Open strQuery, objConn, 0, 4
7	...

Notice the two changes from the previous code:

1. the query string is now simply the name of the saved query in Access,
2. the parameters we use for the recordset object are different (4 from 1), to force the execution of the query.

A reader has noted that Access queries with hyphens in them, like "query-time" need square brackets around them to make them work from ASP, like so: "[query-time]".

Parameterized Queries in Access

The above works fine when you have a simple query, but sometimes it is necessary to pass parameters to a query to make it dynamic.

The diagram above shows how to pass a parameter to the parentID field of the faq_category table. This is shown in the last column in the design window. The parameter is a generic name you give (has to be different from any other field name selected), surrounded by []. The one above reads [categoryID]. Once this is created, go to View > SQL View:

It becomes obvious how Access is passing parameters to the queries. You can have as many parameters as you like in your query. To test the query in Access, go to View > Datasheet View. You should see a popup window come up for every parameter you created, prompting you for input:

Once all the input is taken from you, the query will run and return the results:

Parameterized Queries through ASP

By this point, the hard stuff is actually over. Once you have a working parameterized query in Access, passing a value for that parameter through ASP is pretty easy. For each parameter, append the value to the query name:

1	...
2	Dim numCategoryID
3	numCategoryID = Request.Form("CategoryID")
4	Set objConn = Server.CreateObject("ADODB.Connection")
5	objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/faqs.mdb")
6	Set objRS = Server.CreateObject("ADODB.Recordset")
7	strQuery = "faq_qry_sub_categories '" & numCategoryID & "'"
8	objRS.Open strQuery, objConn, 0, 4
9	...

If you have more than one parameter that you need to pass, simply append them to the query in your ASP. The order has to be the same as the order that the input prompts show up in Access. For example:

1	...
2	strQuery = "faq_qry_sub_categories '" & numCategoryID & "', '" & secondparam & "'"
3	...

Things to watch out for

Any queries that are created inside Access and are called by ADO need to have all "*" replaced by "%" in order for them to work. You can see an example of someone who run into this issue and how they fixed it in a forum post.

Conclusion

Using parameterized queries in ASP and MS Access:

1. allows you to design your queries easier,
2. gives you one central place to maintain your queries,
3. they are faster than regular queries.