Note This tip is especially important for users with limited memory. Avoiding query compilation means that quite a large part of the Jet code will not be used, leaving the memory available for your application to use. See also #7.
A common requirement 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 start 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 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 given 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. Once the parameterized query has been saved and compiled, Jet can do the following, omitting several steps above:
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. However, if you wish to get more values from the user and run the same query with different values, 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 uses only server data, pass-through queries 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.