Can an Access query be protected from being changed/deleted?


I want to protect my query so that changes can't be made. Can the query be changed to read only or protected?
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.

You can hide a query so that, unless the user has the option for 'Hidden Items' turned on in Options|View, the query is not seen.
But that is the extent of the protection offered.

Queries are just text, and can be permanently changed by VBA.
Which means you can write code that will overwrite any changes someone makes to a query back to what you want it to be

Public Sub FixMyQuery()
Dim db as Database
Dim qdf as QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("TheNameOfTheQueryIWantToStayUnchanged")
qdf.SQL = "Select Something from SomePlace where Something = WhatIWant"
End Sub

Run the sub anytime you think that query may have been altered, and it will reset to whatever you code it for.

That's the extent of what can be done to my knowledge.

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:
Can no longer be changed to read-only or protected.

Nick's idea is a good one, because you can protect the VBA project code.

Can no longer be changed to read-only or protected.
That's not an option in A2003+.
How far back was it an option?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
JET 4.0, so A2003 and back.  ULS (User Level Security) can restrict design permissions on a query and also apply "Run with owner permissions" so anyone can still execute it, but not modify it in anyway.

But with ULS gone in ACE (and it's really not, but that's another discussion), it's no longer an option.

But with ULS gone in ACE (and it's really not, but that's another discussion), it's no longer an option.

A reason to stick with .MDB, rather than a lack of reasons to switch
Not that I have seen any good reason to switch to an accdb, since the only things that those file types support are things you shouldn't be using anyway (attachment fields, multi-valued fields and other non-SQL Server compatible abominations) and so there's no operational need or performance benefit to be gained by the switch..

but that's another discussion
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OK, since you brought it up<g>, on the links you posted, it's important to understand that as was in the past, ULS is still there and always on.

 Even with an ACE DB, there's still a work group file and ACE/JET still logs in with a username of 'admin' and a blank password.

 What Microsoft did when they "stripped it out" is simply removed all the ACL's (Access Control Lists) on objects from the DB.

  What I've never confirmed is:

1. If they reset those just on converting to a .accdb, or if they remove them on every compact and repair.

2. If you can still set security attributes through DAO.

  My guess is that they didn't cut much off and you can still set the attributes, but I would suspect that they might drop the ACL's on every compact and repair.

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.