When you use application roles with Access projects, you must consider how Access updates data in forms based on views or stored procedures. The Products database application role is based on a security schema that denies all direct access to tables and uses views and stored procedures instead. The problem you run into is that a form based on an updateable view will be read-only if you try to use the form to update data because Access constructs an UPDATE statement that references the base tables defined in the view, not the view itself. Because the application role doesn't have permissions on the tables, the update fails, which you can see in action if you open a trace in SQL Server Profiler. If you update data from a form based on an updateable view of the Suppliers table, the following code is the first part of the statement that SQL Server processes:
EXEC sp_executesql N'UPDATE "Products"."dbo"."Suppliers" SET
"ContactName"=@P1 WHERE "SupplierID"=@P2 AND ...
To work around this read-only form problem, you have two options: Grant the application role the necessary permissions on tables, or define your view by using the VIEW_METADATA option. Using VIEW_METADATA in your view definition makes SQL Server return metadata information about the view to the client application. Access can then use this additional information to construct an UPDATE statement against the view, not the underlying table. The following code is an example of the syntax you use to create a view with the VIEW_METADATA option:
CREATE VIEW vwShippers
AS
SELECT * FROM Shippers
VIEW_METADATA
Using Application Roles with Access Databases
You can't use application roles with a linked-table Access database (.mdb). The tables are linked through ODBC, not OLE DB, and you have no control over the opening of connections to service these links. Therefore, you have no way to execute sp_setapprole on these hidden connections. Access forms and reports can construct and execute many queries, potentially consuming multiple connections. But you don't have a way of telling ODBC to automatically activate the application role on all these connections.
The only way to use application roles with Access databases is to create an unbound application, using ADO Recordsets or pass-through queries to load all the data and modify it by posting changes through the same mechanisms. The Jet database engine neither parses nor executes pass-through queries in the same way that it parses and executes a regular Access query. The Jet engine passes the SQL statements that comprise the query through to the designated data source so that you can execute commands and run queries directly against SQL Server.
To work effectively with pass-through queries, you must understand several properties that determine their behavior. Aside from setting the pass-through query's SQL property, which contains the text that a pass-through query sends to SQL Server, you must set the Connection string in the query's ODBC Connect Str property. The following Connection string example uses integrated security to connect to the Products database on the local server:
ODBC;Driver={SQLServer};Server=(local);
Database=Products;Trusted_Connection=Yes
Prev. page
1
2
3
[4]
5
next page 
