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