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.
What if we had a long query to pass to our recordset? Consider the following scenario:
<% Dim objConn Dim objRS Dim strSQL Dim numCategoryID numCategoryID = Request.Form("CategoryID") Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/faqs.mdb") Set objRS = Server.CreateObject("ADODB.Recordset") strQuery = "SELECT faq_category.ID, " _ & "Count(faq_category.name) AS count, " _ & "faq_category.name, faq_category.description "_ & "FROM faq_category " _ & "INNER JOIN faq_faq ON faq_category.ID = faq_faq.category_ID " _ & "GROUP BY faq_category.ID, faq_category.name, " _ & "faq_category.description, faq_category.parentID " _ & "HAVING faq_category.parentID=" & numCategoryID _ & " ORDER BY faq_category.name;" objRS.Open strQuery, objConn, 0, 1 '-- continue with writing your recordset out ... %>
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.
Using Access’ 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:
... Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/faqs.mdb") Set objRS = Server.CreateObject("ADODB.Recordset") strQuery = "faq_qry_sub_categories" objRS.Open strQuery, objConn, 0, 4 ...
Notice the two changes from the previous code:
- the query string is now simply the name of the saved query in Access,
- 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:
... Dim numCategoryID numCategoryID = Request.Form("CategoryID") Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/faqs.mdb") Set objRS = Server.CreateObject("ADODB.Recordset") strQuery = "faq_qry_sub_categories '" & numCategoryID & "'" objRS.Open strQuery, objConn, 0, 4 ...
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:
... strQuery = "faq_qry_sub_categories '" & numCategoryID & "', '" & secondparam & "'" ...
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.
Using parameterized queries in ASP and MS Access:
- allows you to design your queries easier,
- gives you one central place to maintain your queries,
- they are faster than regular queries.
This is a great post and an area of interest for me. There’s one thing I don’t understand.
I am using the DW8 method for calling a query – here’s an example:
rsRecordsetname_cmd.CommandText = “SELECT * FROM qrySomequery”
Now the query “qrySomeQuery” prompts for two values (in Access). On the webpage where the rsRecordsetname will be used, I will passing values for these paramaters using a query string (ie., on the URL).
SO, MY QUESTION is about the syntax of the recordset CommandText – how do I ‘feed’ those request.querystring values to the recordset?
You would simply pass the URL values into the query:
The order of the parameters is simply determined by the order in which they appear in your Access query.
Thank you so much for this. I was most of the way there but struggled on the final parts. But all is sorted now thanks to this page. Thank you! Steve
I am happy this was helpful!
i spent hours trying to understand why it isnt working, apparently it is because of * to % issue. You saved my life 🙂