locked
Access Project and Views RRS feed

  • Question

  • SQL Server security allows that a user can select, update, etc. records in a view (providing the user has these permissions on the view) without that user having any permissions on the underlying table.  This does work correctly when executing a T-SQL statement using a select or update statement in query analyzer.  However, I am using a Microsoft Access Project as my front end and this functionality does not work when trying to update the same view through an Access form in the Project.  Is there some extra step I need to take in order to be able to use this functionality via an Access Project form?  Or is there some work-around to be able to use this security feature (which, frankly, would solve all my problems) in Microsoft Access Projects?

    Thanks so much for any help.

    Friday, January 20, 2006 5:36 PM

Answers

  • If I understand your problem correctly, one solution requires ADODB objects that supply the parameter(s) the user enters on the form and the name of the stored proc on the SQL back end that 1).drops the view from the db, 2).runs a query using the supplied parameter(s) that 3). recreates the view using the new parameter(s).  You might also create a function that returns a table having the desired results, since you can make the function the record source for a from or report.  I am researching a similar problem, an Access report that in an Access project that users print to obtain the result of a search done using parameters supplied by form entries.  My problem is, the report fails to work unless users have Modify-Write permissions on the Access Project .adp file!!

     

    Anyway, the following is from the MSDN site here (and I have misplaced the URL!!!).  It may help build the code you need to get the right data to your users.  Please nots that the columns referrred do not exist.  Instead, the stored project statements appear first, then the function statements are listed.  Hope this helps.

     

    In SQL Server, the query can become either a view and a stored procedure or a 
    single user-defined function. The result set of these solutions cannot be 
    updated. The view has a derived column in it and the result sets of user-defined 
    functions are not updatable. 
    View and stored procedure	User-defined function 
     
    DROP VIEW "No_of_Orders" 
    GO 
    CREATE VIEW "No_of_Orders" 
    AS 
    SELECT CompanyName, City, Region, Country, 
    (SELECT Count(*) 
    FROM Orders 
    WHERE Orders.CustomerID = Customers.CustomerID) 
    AS Number_of_Orders 
    FROM Customers 
    GO 
     
    DROP PROCEDURE OBC 
    GO 
    CREATE PROCEDURE "OBC" @CountryName Char(15) 
    AS 
    SELECT * FROM No_of_Orders 
    WHERE No_of_Orders.Country = @CountryName 
    ORDER BY CompanyName 
    GO 
    	 
     
    CREATE FUNCTION udfCompOrders(@CountryName varchar(30)) 
    RETURNS @tCompOrders TABLE 
    ( 
    companyname varchar(80), 
    city varchar(30), 
    region varchar(30), 
    country varchar(30), 
    number_of_orders int 
    ) 
    AS 
    BEGIN 
    INSERT @tCompOrders 
    SELECT companyname, city, region, country, (select count(*) FROM orders WHERE 
    orders.customerid = customers.customerid) AS number_of_orders 
    FROM customers 
    WHERE country = @CountryName 
    ORDER BY companyname asc 
    RETURN 
    END 
    GO 
     
    In the first column of the preceding table, the stored procedure queries the 
    view, filters the results according to the CountryName parameter, and then sorts 
    the output according to the ORDER BY clause. All of this logic can be 
    encapsulated into one user-defined function, including the parameter and the 
    order by clause, as shown in the second column. 
     
    When making your decision, you should determine whether there are individual 
    units of logic in the query that might be needed by other queries. If this is 
    the case, you might consider breaking your query up into multiple views, stored 
    procedures, and user-defined functions so that your logic can be reused. 
    Nested Queries 
     
    Nested queries are one of more queries contained within another query. The 
    nested query often acts as a data source for the nesting query. Nested queries 
    can be migrated to SQL Server as user-defined functions or nested views. In some 
    queries the nested query contains parameters, and the nesting one does not. In 
    this case, you must use a user-defined function because views cannot take 
    parameters. 
    Sunday, February 12, 2006 7:27 PM
  • Here's some additional info from our support engineer, including the possible URL that Geezer referred to.

    Base my understanding ,your question is about securing SQL SERVER Through Access. I hope the following article is helpful. If any misunderstanding, please tell me.

    Securing SQL Server Through Access

    http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=8552&DisplayTab=Article

    -brenda (ISV Buddy Team)

    Monday, February 13, 2006 6:06 PM

All replies

  • If I understand your problem correctly, one solution requires ADODB objects that supply the parameter(s) the user enters on the form and the name of the stored proc on the SQL back end that 1).drops the view from the db, 2).runs a query using the supplied parameter(s) that 3). recreates the view using the new parameter(s).  You might also create a function that returns a table having the desired results, since you can make the function the record source for a from or report.  I am researching a similar problem, an Access report that in an Access project that users print to obtain the result of a search done using parameters supplied by form entries.  My problem is, the report fails to work unless users have Modify-Write permissions on the Access Project .adp file!!

     

    Anyway, the following is from the MSDN site here (and I have misplaced the URL!!!).  It may help build the code you need to get the right data to your users.  Please nots that the columns referrred do not exist.  Instead, the stored project statements appear first, then the function statements are listed.  Hope this helps.

     

    In SQL Server, the query can become either a view and a stored procedure or a 
    single user-defined function. The result set of these solutions cannot be 
    updated. The view has a derived column in it and the result sets of user-defined 
    functions are not updatable. 
    View and stored procedure	User-defined function 
     
    DROP VIEW "No_of_Orders" 
    GO 
    CREATE VIEW "No_of_Orders" 
    AS 
    SELECT CompanyName, City, Region, Country, 
    (SELECT Count(*) 
    FROM Orders 
    WHERE Orders.CustomerID = Customers.CustomerID) 
    AS Number_of_Orders 
    FROM Customers 
    GO 
     
    DROP PROCEDURE OBC 
    GO 
    CREATE PROCEDURE "OBC" @CountryName Char(15) 
    AS 
    SELECT * FROM No_of_Orders 
    WHERE No_of_Orders.Country = @CountryName 
    ORDER BY CompanyName 
    GO 
    	 
     
    CREATE FUNCTION udfCompOrders(@CountryName varchar(30)) 
    RETURNS @tCompOrders TABLE 
    ( 
    companyname varchar(80), 
    city varchar(30), 
    region varchar(30), 
    country varchar(30), 
    number_of_orders int 
    ) 
    AS 
    BEGIN 
    INSERT @tCompOrders 
    SELECT companyname, city, region, country, (select count(*) FROM orders WHERE 
    orders.customerid = customers.customerid) AS number_of_orders 
    FROM customers 
    WHERE country = @CountryName 
    ORDER BY companyname asc 
    RETURN 
    END 
    GO 
     
    In the first column of the preceding table, the stored procedure queries the 
    view, filters the results according to the CountryName parameter, and then sorts 
    the output according to the ORDER BY clause. All of this logic can be 
    encapsulated into one user-defined function, including the parameter and the 
    order by clause, as shown in the second column. 
     
    When making your decision, you should determine whether there are individual 
    units of logic in the query that might be needed by other queries. If this is 
    the case, you might consider breaking your query up into multiple views, stored 
    procedures, and user-defined functions so that your logic can be reused. 
    Nested Queries 
     
    Nested queries are one of more queries contained within another query. The 
    nested query often acts as a data source for the nesting query. Nested queries 
    can be migrated to SQL Server as user-defined functions or nested views. In some 
    queries the nested query contains parameters, and the nesting one does not. In 
    this case, you must use a user-defined function because views cannot take 
    parameters. 
    Sunday, February 12, 2006 7:27 PM
  • Here's some additional info from our support engineer, including the possible URL that Geezer referred to.

    Base my understanding ,your question is about securing SQL SERVER Through Access. I hope the following article is helpful. If any misunderstanding, please tell me.

    Securing SQL Server Through Access

    http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=8552&DisplayTab=Article

    -brenda (ISV Buddy Team)

    Monday, February 13, 2006 6:06 PM