Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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. Import the tblQueryDefs table, the frmSavedQueries form, and the
   basSavedQueries module from

   01-16.MDB into your
   database.

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

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

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