Hiding Your Query (Design) in Forms and VBA Code

You can make some of your queries visible to fewer people by storing the design in a form or in a module. The biggest benefit here is that there is far less chance that the average user will accidentally change your query if you are not using workgroup security. You can use these places to store your queries:

• In the RecordSource property of a form or a report.

• By saving the SQL code of the query in VBA code as a string variable and assigning the RecordSource property of a form or a report when opening the form.

• In VBA code rather than in the Database window, if you are using the DAO and ADO RecordSet property.

Remember that if you store your query in VBA code, you should consider using both a VBA project password and module protection in the Database window (described in Chapter 8). Of course, workgroup security on your form will also make the VBA code and the RecordSource property harder to find, as will the appropriate startup options (see Chapter 2).

Tip  

Though it makes sense to hide the queries in VBA code and the RecordSource property, do not store the same query twice in different places. If you have to, store the SQL in a function and call the function to return the value.

Encrypting a Database to Obscure the SQL in Queries

In both of the query protection issues I've described in this chapter, you need to be aware that a query's SQL is small and readily distinguishable when viewing a normal decrypted database in a text editor. If you are totally serious about hiding the query's design, you may want to encrypt the database by choosing Tools ˜ Security ˜ Encrypt/Decrypt Database or by using the protected folder strategy discussed in Chapter 12. This option also applies to the MDE format as described at the end of the chapter.

Now I will show you a neat trick that will turn off the Design View button for forms and reports . As you read that material, you should gain an insight into the virtues of class objects.