Access - allow updates of data, but only through forms

Question

Is it possible, in Microsoft Access 2007, to allow a user to select and update data only when they are editing via a form?

I have a form which contains a datasheet of a foreign table (which in turn has another subtable). I want the users to only edit their own content via the datasheet in the form.

Running a SQL backend, so stored procedures can be used and they can only execute the ones they have EXECUTE rights to.

Edit:

Can the table be hidden in Access and the user unable to show it, unless they are the database owner? Alternatively, how can you have subdatasheets run a stored procedure to update, instead of editing the table directly? I want to keep the ability to 'drill down' into related tables.

Answer 1

I think you have answered your own question.

You can use stored procedures to retrieve and update your data, and take away any linked tables or remove UPDATE permissions to them.

You would lose the convenient data binding in the form fields, which you'd have to remodel in VBA.

Alternatively (to keep the data binding) you could point the form to a read-only data source and catch the "Before Update" event to do the actual update, before Access ties to save the updates itself. This is just an idea though, I have not tested if it works.

Answer 2

If you're willing to delve into Jet User-Level Security, you can make your linked tables non-readable by users and use RWOP (Run With Owner Permission) queries to allow editing in the forms. You would, of course, remove access to the database window in order to prevent them from running the RWOP queries. That can also be enhanced by putting a reference to the form you're using the RWOP query in that would prohibit it from returning records unless it's run inside the form.