Using a stored proc as a forms recordsource

[stuartb113 via AccessMonster.com]

For an ADP file, I have created a form whose record source is a stored proc.
i.e.
CREATE PROCEDURE up_Instrument_list
AS
Select * from dbo.Instrument
GO

The forms should be updatable to a specific role (Trader) and read only to
another.
EXECUTE rights have been assigned to the stored proc for both roles and
UPDATE rights have been applied to the underlying table (dbo.instrument) to
just the Trader role.

The issue is, I also have to assign SELECT rights on dbo.instrument to the
Trader role for the recordset to be updatable - otherwise form's recordset
remains readonly.
Could someone please explain...
I'd hoped to utilise Access and stored proc's to allow the users to update
data but prevent them from having direct select access to the tables.

thanks in advance,
Stuart

 

[TC]

If you want to ensure that the users can only do data entry, and can
not retrieve existing records, you can do that by some simple code
within the form. Set the form's DataEntry property to true, to ensure
that it starts off in data entry mode. Then in AfterInsert(?) of the
form, do the appropriate statement to put it back into Add mode. Sorry,
I don't have Access here to check, so I can not give you the specifics,
but it should be easy to do.

HTH,
TC (MVP Access)
http://tc2.atspace.com