You could, of course, create your own pooling scheme by holding multiple connections open and allocating them as needed, but setting up a pooling scheme isn't a trivial task. In typical client/server applications, each client could reduce overhead by holding open one global Connection object for the duration of the application, provided that your licensing can support the requisite number of open connections.
The code in Listing 1 uses a Connection string (strConn) that specifies integrated security (Integrated Security=SSPI) and turns off resource pooling (OLE DB Services = -2). After the connection opens, the sp_setapprole stored procedure executes immediately, activating the application role, which remains in effect as long as the connection stays open.
The code in Listing 2 runs in a command button's Click event procedure and opens an ADO Recordset by using the Connection object that the code in Listing 1 created. The code in Listing 2 then assigns the ADO Recordset to an MSHFlexgrid control's DataSource property. After you activate the application role, you can open and close any number of ADO objects over the same connection; you need to activate the application role only once. The activation remains in effect until you explicitly close the Connection object, as the Unload event of the form that the code is running in shows in the following example:
Private Sub Form_Unload(Cancel As Integer)
mcnn.Close
Set mcnn = Nothing
End Sub
Using Application Roles in Access Projects
Using an application role in a Microsoft Access project (.adp) is straightforward because an Access project requires only one OLE DB connection to SQL Server. (For more information about downloading this article's sample Access project and its database code files, see the More on the Web box, page 52.) Access's CurrentProject object has an OpenConnection method you can use to create the connection programmatically. (Make sure you turn off connection pooling in your connection string.) After you have connected the project to SQL Server, you use CurrentProject.Connection's Execute method to activate the application role. This example conditionally executes the sp_setapprole stored procedure based on CurrentProject's IsConnected property*if you aren't successful in connecting to SQL Server, you'll trigger a runtime error only if you try to activate the application role, as Listing 3 shows.
To disconnect from SQL Server and deactivate the application role, use the CurrentProject object's CloseConnection method:
If CurrentProject.IsConnected Then
CurrentProject.CloseConnection
End If
To create the connection in code, you must create a project with an empty connection by canceling the Data Link Properties box when creating the new Access project. Add your own startup login form that opens the connection to SQL Server and activates the application role.
Prev. page
1
2
[3]
4
5
next page 
