Q: (from Gregory)
I am working with a variety of non-profits to help them collect and analyze various kinds of data. In some cases, it would be desirable to have a system whereby different permissions could be assigned to different users of these databases regarding which tables the user could access, which forms they could open, etc. Also, some users need read/write access where others should only have read access. Up through Access 2003 MS had the User level security wizard in Access which created a system for exactly this need. Now that system has apparently been discontinued and no longer supported by Access 2007, but I have not been able to find out if there is a replacement. What is the new best practice for setting user permissions on objects in an Access database?
A:
Regarding permissions in Access: what I’ve found from the Access online help (I searched on “permissions”; perhaps this is where you’ve already looked)
The new Access file format does not support user level security; so as I understand your choices (as documented by the online help)
- Adopt the new Access file format and utilize SharePoint to manage user security
- Stick with the old Access file format and apparently Access 2007 will re-expose the user permissions controls under the Database Tools tab
- Adopt a more robust back-end data solution like SQL or SQL Express
I don’t feel any of these are great solutions for you; but I have nothing else to offer. The impression I have is that since Access is categorized as a “user level” database solution; to continue to support a robust security model did not make sense. Access is targeted at straight-forward solutions and that stepping up to a solution like SQL or SharePoint (which shares SQL on the back end) provides our robust secure data solution.
Response: (from Gregory)
Thanks for the reply, Steve. I had come to the same conclusion as you regarding my options. I don’t really like the choices. I have a SharePoint site, but using that for security for an Access database, from what I have read, will involve many changes to the database which are not really acceptable to me. In short, there are many features of Access which will not be supported when it’s accessed through SharePoint. I don’t want to stick with the 2003 file format. That would leave me stuck in the past and unable to ever upgrade. Not something I want to do. And I’m not really anxious to learn SQL right now, as I’m just a lowly IT director and have about a million other hats. I can’t devote the time to becoming a real database admin.
I wonder if the folks in the Access division realize how many of us are using Access for some pretty sophisticated solutions involving many users. It’s their fault if they’ve built Access into a product which does a very nice job in a wide range of scenarios and is, while not easy to learn, still much easier than something like SQL. Maybe you could pass along my feedback to them that many of us in the small to medium business category would really like to see some help with user level security in Access, without involving SharePoint.
I would be very happy to provide more detail on what kinds of things I’m doing with Access, if anyone would find that helpful.
A:
I’ve forward your feedback though our internal channels, but I would recommend you (and any associates) send your feedback to the Access team directly. You can do this at:
http://office.microsoft.com/en-us/access/default.aspx
On the left side of the screen, you will see a link titled: “Send us your comments”