Web Developer Chicago Public Radio US-IL-Chicago Post A Job | Post A Resume

Parameterized Queries in Access
Henrik Blixt

---

Note from Scott Mitchell
Parameterized queries are a great asset when developing database applications. They allow you to modularize your code, so that it's easier to read and maintain. Another great benefit of parameterized queries is that they run faster than queries which you explicity type in each time.

You can use parameterized queries in both MS-SQL and MS-Access. This article discusses how to use parameterized queries in Access; if you'd like to see how to do it in SQL, read Using Parameterized Queries.

Scott Mitchell

---

Here is a sample for a parameterized stored procedure in MS Access. The SQL in Access is: PARAMETERS [inid] Text; SELECT ... and on.

And the source is:

<%@  Language=VBScript %>
<%
	Dim cnn1
	Dim cmdNyttSvarID
	Dim prmNyttSvarID
	Dim rstNyttSvarID
	Dim strID
	Dim strCnn
	Dim strSize
	Dim i
	Dim tmpFields
	Dim strName
	Dim strTable
	
	' Open connection.
	Set cnn1 = Server.CreateObject ("ADODB.Connection")
	strCnn = "DSN=agenda21"
	cnn1.Open strCnn
	cnn1.CursorLocation = adUseClient
	
	' Open command object with one parameter.
	Set cmdNyttSvarID = Server.CreateObject ("ADODB.Command")
	cmdNyttSvarID.CommandText = "nyttsvarid"
	cmdNyttSvarID.CommandType = adCmdStoredProc
	
	' Get parameter value and append parameter.
	' The value for the one parameter in this example
	strID = "00001"
	strSize = Len(strID)
	
	' Parametername
	strName = "inid"
	
	Set prmNyttSvarID = cmdNyttSvarID.CreateParameter(strName, adVarChar, adParamInput,strSize,strID)
	cmdNyttSvarID.Parameters.Append prmNyttSvarID
	prmNyttSvarID.Value = strID
	
	' Create recordset by executing the command.
	Set cmdNyttSvarID.ActiveConnection = cnn1
	Set rstNyttSvarID = cmdNyttSvarID.Execute
	i = 1
	
	' Dump the returned recordset to the client
	'set rstNyttSvarID = Server.CreateObject ("ADODB.RECORDSET")
	'set tmpField = rstNyttSvarID.Fields.Item (1).Value 
	
	Do While Not rstNyttSvarID.EOF
		Response.Write ("<BR>")
		
		For Each tmpField In rstNyttSvarID.Fields
			Response.Write (tmpField.Name & ":" & tmpField.Value & ",")
		Next
		
		rstNyttSvarID.MoveNext
	Loop
	
	rstNyttSvarID.Close
	cnn1.Close
%>

Alter 4Guys reader Peter J. writes: "First and foremost, your article uses a DSN to access the MS Access database, which is unstable under 24x7 web site conditions. Follow this link for details: http://www.adopenstatic.com/faq/whyOLEDB.asp "Second, you make the following claim: "Another great benefit of parameterized queries is that they run faster than queries which you explicity type in each time." Conceptually, this makes sense, since stored queries are supposedly compiled for better performance. Indeed, this seems to be the case for SQL Server and Oracle. But in the case of Access, I think the overhead of running a stored query from ASP will cancel out any small benefit you may get. In my case, I ran timed examples both ways, and my particular query was just as fast in the ASP page, as it was stored in the database. "Have you had any experience where the query is faster in the Access database than it is in the ASP page? I would encourage you to research this topic, and if appropriate, change the ASP page to reflect your results. I will likely run a few tests myself when time permits."

Happy Programming!

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article