Access Cookbook - Ken Getz [33]
Figure 1-44. The saved queries form, frmSavedQueries
To use a saved query in your code, search the tblQueryDefs table for the name of a query and get the value from the SQLText field. To use this technique in your application, follow these steps:
Import the tblQueryDefs table, the frmSavedQueries form, and the basSavedQueries module from 01-16.MDB into your database.
To add a query to the tblQueryDefs table using the frmSavedQueries form, design and test the query using the Access query designer. Then, from query design view, select View → SQL. When the query's SQL string is displayed, highlight it and copy it to the clipboard. Next, add a new record in the frmSavedQueries form and paste the SQL string into the SQLText text box. Type in a name and description.
To get the SQL string of a saved query, use the acbGetSavedQuerySQL function, located in the basSavedQueries module. The syntax for this function is:
strSQL = acbGetSavedQuerySQL("queryname")
where strSQL is the string variable in which you want to store the query's SQL string and queryname is the name of the saved query you want to retrieve.
Discussion
The core of this technique is a simple function that retrieves a value from the tblQueryDefs table. The function uses the Seek method to find the supplied value and, if it finds a match, returns the record's SQLText field value.
Public Function acbGetSavedQuerySQL(strName As String) As String
' Returns a SQL string from tblQueryDefs
' In : strName - name of query to retrieve
' Out : SQL string
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb( )
Set rst = db.OpenRecordset("tblQueryDefs")
rst.Index = "PrimaryKey"
rst.Seek "=", strName
If Not rst.NoMatch Then
acbGetSavedQuerySQL = rst!SQLText
End If
rst.Close
Set rst = Nothing
Set db = Nothing
End Function
(If you import this module into an Access 2000 or later database, make sure to use the Tools → References menu item to add a reference to the Microsoft DAO type library. The code uses DAO objects, and later versions of Access don't reference this library by default.) By extending this technique, you can create a replacement for saved queries in Access. Because you have full programmatic access to each query, you can load, modify, execute, and save queries at will without having to open QueryDef objects. Additionally, because you can store the queries table in a library database, you can completely remove a user's access to saved queries except through your code. One drawback of this technique is that you cannot base forms or reports on queries saved in tblQueryDefs without using some VBA code. However, this drawback is easily overcome by writing a function that retrieves a saved query's SQL string from tblQueryDefs and assigns the value to the form or report's RecordSource property before the form or report is run.
An obvious enhancement to this technique would be a conversion routine that reads each of your database's saved queries and converts them to records in the tblQueryDefs table. Once this conversion is complete, you can delete the queries from the database window.
TIP
Using saved queries gives you a slight performance advantage over saved SQL strings. The Jet database engine creates and saves a query plan the first time it runs a query after the design has been saved. With saved queries this plan can be reused, but with ad hoc queries a new plan must be generated each time. The time required to generate these plans, however, probably will not noticeably impact your performance. There are also ways to hide saved queries from