Another option for pass-through queries is to create a Data Source Name (DSN) to use in the Connection string, but this option requires that you configure the DSN on each user's machine. Simply specifying the connection information above might be an easier option to maintain.
Access is unpredictable about the precise time when it opens a new ODBC connection. A pass-through query that provides data to an Access object might execute once or more than once, depending on the object and the user's behavior. Or, a pass-through query might open a new connection or use a connection that's already open.
If you run the sp_setapprole stored procedure and the application role is active, you'll get a runtime error when the pass-through query executes. To avoid this error, you can code into the pass-through query some logic that tests whether the T-SQL user_name() function returns the application role's name. This logic provides a reliable way to ensure that you won't execute sp_setapprole on a connection that already has an application role in effect. The following example conditionally executes the sp_setapprole stored procedure, then executes the procProductList stored procedure:
IF (SELECT USER_NAME()) <> 'ProductApprole'
EXEC sp_setapprole 'ProductApprole', 'password'
EXEC procProductList
Figure 5 shows the completed pass-through query definition and the query's property settings, including the specification that the query returns some data. Note that Access pass-through queries can't handle multiple resultsets. If the stored procedure returns more than one resultset, Access processes only the first one and ignores any remaining resultsets.
You don't have to use the Access UI when you create and modify pass-through queries. You can manipulate pass-through queries in VBA code, using either the DAO or ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) object libraries. DAO is the older object library, but Microsoft created and optimized DAO specifically to work with the Jet engine. When you're working with Jet objects, DAO usually gives you the best feature selection and performance.
To use DAO in Access 2000, you must first set a reference to the Microsoft DAO 3.6 object library, if such a reference doesn't exist. If you're working with an earlier version of Access, use the DAO version appropriate to that release. To modify a pass-through query, create a DAO QueryDef object and modify the object's properties. Listing 4 shows the code you might use to modify a pass-through query's SQL property, then execute the query.
Instead of modifying an existing pass-through query or creating a new saved query, you can create a temporary pass-through query by giving the query a zero-length name, as the following command shows:
Set qdf = db.CreateQueryDef("")
Or, you could use ADOX to modify a pass-through query. The logic is the same as DAO, but the syntax differs. You can find examples of how to use DAO and ADOX to modify queries in the approlemdb.mdb example file.
To hide the design of your pass-through queries from your users, you could use Access security to keep Access from exposing the application role's name and password. Or, you could limit your use of pass-through queries to those in VBA code, which you can protect by converting your Access .mdb files to Access .mde files.
Powerful and Convenient
Application roles can provide a powerful and convenient way to administer security in client/server scenarios. Application roles eliminate the need to enable multiple database users, and the permissions granted to an application role are available to your users only when they run your application.
The sp_setapprole stored procedure activates an application role, and you can execute this stored procedure from ADO, DAO, or RDO code or from an Access pass-through query. You can also use application roles with bound VB applications that use the Data Environment designer. The most important limitation of application roles is that you can't currently use them with pooled OLE DB connections, making them unsuitable for use in middle-tier objects. With that limitation in mind, you should consider the convenience of using application roles when users are running client/server programs that directly access SQL Server.