How can I prevent a user from deleting adding or editing data in a query?

I use Access 2010.

How to prevent a user from deleting, adding or editing data in a query?

I thought there was a way to write a query that would prevent changes to the data.

With the old User Level Permissions I could protect the data in a query so that users could open and read the data in the query, but could not change the data in the query. Is there a way that I can manage that in Access 2010?
Fritz PaulAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ramprakash JeyabalanCommented:
You can simply use GRANT. Refer the MS site for syntax and more details.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You cannot without using a back end other than ACE to store the data (something like SQL Server)

All data control in Access with a ACE data store (what you think of as "Access" out of the box)  is achieved through forms.

Fritz PaulAuthor Commented:
Thanks. Please give me an example. Below is a SQL satement. How will I apply Grant to this statement so that no one can add or edit data in this query?

SELECT TblPlantsPrices.PlantID, TblPlantsPrices.SoldAs, TblPlantsPrices.Price
FROM TblPlantsPrices;

Open in new window

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Fritz PaulAuthor Commented:
My previous request was for Ramprakash Jeyabalan. Sorry I did not see that Jim replied in the mean time.
I haven't used the GRANT statement personally and am not familiar with its usage, but according to that MSDN article, it applies to versions 2013 and higher -- so it does not apply to your A2010 database.

Seconding Jim's comment, you should use forms to control access to your data.  Using a form in datasheet view gives your users the same look/feel of a query, but it puts YOU, the developer, in control of who has what permissions on the data.
Dale FyeOwner, Developing Solutions LLCCommented:
I agree with Jim and Miriam that presenting data in forms is the preferred method.  However, if you are insistent on opening a query for your users to see, you can do it using the DataMode argument of the OpenQuery method

docmd.OpenQuery "qry_frm_Client_Divisions_List", , acReadOnly

this will cause the query to open as read only.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<My previous request was for Ramprakash Jeyabalan. Sorry I did not see that Jim replied in the mean time.>>

The thing to understand here is that GRANT, REVOKE, etc were added to JET SQL to work with ULS (User Level Security), which has been removed with ACE (A2007 up).  They also apply to containers, which is how Access itself works with the database engine and which not many would ever deal with.   But that doesn't protect data, but rather the objects themselves.

 So if you have an .accdb then they are not going to work for you because everything is under the user 'admin'.    Users and groups don't exist per say anymore (they actually do, but every object in the DB is setup for the admin user - that's how ULS was stripped out by Microsoft).

Fritz PaulAuthor Commented:
Dale, :) you answered my question 100%. I did not think of that.

However I have to say that every comment made on this question taught me something very valuable.

By the way the following reference explains that "Grant" also works for Office 2010. However I can't figure out how and I don't need it anymore.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<By the way the following reference explains that "Grant" also works for Office 2010. However I can't figure out how and I don't need it anymore.>>

 It also applies to 2007 and prior as well, all the way back to A2000.

 But it only works in the sense that if your dealing with a MDB format, you may have users and groups.  If not (your using .accdb format, which is A2007 and up), then it doesn't apply as ULS was stripped out.

 They also work on document containers, but no one messes with them because there really is no reason to.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.