Best way to make a query read only?

[Remster]

Folks



What's the best way to make a query read-only? I want my users to be able to run queries, but I want them to have to use a form to make any changes to data (because the form contains lots of data validation features). I've read that one way is to set the Recordset Type of the query to Snapshot, but while this seems to do the trick, I have a sense that this property isn't intended for that purpose.

Remster

[jzwp11]

Your users should not have access to the database window. All of their interactions with the database should be through forms you create. I would use a form that has a command button on it that opens another form that is based on the query. In that second form, you would check to make sure that they are doing the validation as intended.

[Remster]

Many thanks for your reply.

My 'users' will total only four – myself, my immediate colleague, my manager and my manager's manager – so there isn't any danger of abuse, though there is a danger of mistakes. On the other hand, they're going to be requesting a lot of data on an ad hoc basis, so I'm not going to have time to go into the intricacies (for me) of coding data validation for a form every time they put in a request. That's why it would be helpful to be able to set my queries to read-only. In the light of this, would you still recommend creating forms for the sole purpose of viewing data in datasheet view (I realise I could create them using the Autoform facility and disallow all editing)?

[jzwp11]

I've never tried the snapshot option you mentioned in your original post, but that would only apply to the recordset you would open with the query in code. The users would not be able to see the contents of the recordset.

I do not think there is a way to control data changes to a query result when the query is run directly from the database window. The only way that I know of is to base a form on the query and apply the control via the form.

I'm kind of at a loss of how else to direct you, perhaps others on the forum may have additional thoughts.

[Remster]

I've found this thread on the same subject, which I'll assume solves the problem, though I'm going to follow your advice and give my users access to forms and reports only – I'll create read-only forms in datasheet view based on my queries.