Recipe 1.16 Save My Queries in a Table for Better Programmatic Access and Security

1.16.1 Problem

Your application uses a lot of queries, and you don't want these queries available or even visible to the users of your application. Also, you call your queries from VBA code. How can you hide the queries from users and make them easier to retrieve, modify, and execute?

1.16.2 Solution

You can create a query-management table that stores the SQL string of your queries in a memo field. Each query is named and includes a description. This technique allows you to store your queries in a table rather than in the Access collection of queries. You can also create a simple VBA function that you can use to quickly retrieve the SQL string of any of your saved queries.

Open and run frmSavedQueries from 01-16.MDB. After a few moments of processing, the form shown in Figure 1-44 should appear. This form is based on the tblQueryDefs table, which stores a record for each query you save. To add a new query to the table, add a new record and enter the SQL statement in the SQL Text control. You may find it easier to copy the SQL from an existing query (see Step 2 for more details). Type in a name and description. Notice that creation and modification times are automatically updated.

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:

1.16.3 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.

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 users—you can give them names that start with "Usys" or set their Hidden property. You can also protect their design using Access security. Nevertheless, it is useful to understand that queries can be encapsulated in SQL strings, since you may find it helpful to be able to manage them yourself in a table rather than as Access objects.