A common requirement that applications have is to build a SQL string based on values that the user provides. This often leads to code that looks like the following, which finds all authors whose name begin with a given letter firstletter:
Dim d As Database, rs As recordset
Set d = OpenDatabase("biblio.mdb")
firstletter = "G"
sqlstr = "Select * from Authors where Author like """ & firstletter & "*"""
Set rs = d.OpenRecordset(sqlstr)
Debug.Print rs!Author
This code uses Microsoft Basic to build a SQL string and then asks Jet to execute that SQL statement. This technique is often referred to as using dynamic SQL. Some of the reasons for using this are:
Unfortunately the penalties with doing this can be quite high – especially when using ODBC data. The main steps that happen are:
1. Jet parses the SQL string.
2. Jet compiles the SQL string, determining which parts go to the ODBC server.
3. Jet builds a server specific query and sends it to the ODBC server.
4. The ODBC server parses and compiles the SQL string.
5. The ODBC server retrieves the results and returns them to Jet.
6. DAO looks at the results and builds the appropriate recordset and field objects.
Another alternative to creating SQL strings dynamically is to use parameterized queries that you create ahead of time. These are queries that have variables in place of actual values in the SQL statement. Your code gathers the values for these variables from the user and then tells the engine what they are. When the parameterized query has been saved and compiled once, Jet can do the following, omitting several of the above steps:
1. Jet sends a parameterized query to the ODBC server.
2. The ODBC server parses and compiles the SQL string.
3. Jet passes the values for the parameters.
4. The ODBC server retrieves the results and returns them to Jet.
Already this is less steps than above. If you wish to get more values from the user and run the same query with different values, however, you only need to repeat steps 3 and 4. This is a substantial saving, especially for client-server applications. (Note: If you're not interested in updating the results and the query only uses server data, a pass-through query might be even better.)
To do this in DAO, you use the Parameters collection on a Querydef. For example, the following code creates the query (which you would normally do once through the Microsoft Access query designer):
' This code only executed once - or done in query designer
Dim qd As querydef
Set qd = d.CreateQueryDef("AuthorLike", "PARAMETERS FirstLetter Text; SELECT DISTINCTROW Authors.Au_ID, Authors.Author FROM Authors WHERE ((Authors.Author Like [FirstLetter]))")
qd.Close
Once the query is created, you can set the parameters from code as follows:
firstletter = InputBox("Enter first letter of author's name")
Set qd = d.OpenQueryDef("AuthorLike")
qd!firstletter = "A*"
Set ds = qd.CreateDynaset()
...
qd!firstletter = "S*"
Set ds = qd.CreateDynaset()
As you can see, the code is shorter, easier to understand, easier to maintain – and actually runs faster as well.