Question Access 2010 - Looking to get some user level security ideas

[mvanella]

In the small business that I work for, we have a small ( < 5MB) access database with a front end application. We subscribe to office 365 so we have sharepoint available if we want to move there in the future and the tables are all web compatible. We host our back end on our local on site server and we have access installed for all workstations. There are less than 10 users and I would like to add some security to the database. Not that we don't trust anyone who works here or that the data is especially proprietary, but we would like to prevent accidental deletion or data corruption. I went with access mainly because I am lazy and I don't want to reinvent the wheel with a WAMP database application. We use Windows SBS 2008 and we all have our own domain logins, I was hoping to use that to my advantage with the DB since it is used on windows in our domain while we are logged in as specific users.

So I have been poking around the forums and google and I have seen a few ideas and objections with each idea. I would like to put this to the forum to see some brainstorming and ideas bouncing around.

Would anyone recommend or non recommend a move to an SQL back end?

I know that since 2007, and the introduction of the .accdb file, MS has done away with user level security; is there a viable workaround?

If anyone has any ideas as to how to implement more user level security in an Access 2010 application.

And finally, would any of these situations be able to be compatible with a browser based implementation for future, cost effective, access to the DB (as in not having to have copies of Access on each workstation and thus the cost of the license)?

Thanks for any info.

 

[Pat Hartman]

Use Active Directory to secure the folder where the ACE BE resides. That will limit access to only authorized users. It won't stop anyone with access to the folder from copying the BE which is the biggest weakness of a desktop database. Going to SQL Server will give you much more data security and it will allow you to create read-only users which isn't really possible without code with Jet/ACE.

Sadly, no current Access solution is compatible with a browser based implementation. You can convert the data but you will have to start from scratch on the FE and even worse is that you will be limited to macros since VBA is not supported in Access web apps.

but we would like to prevent accidental deletion or data corruption

You can trap delete requests and prompt for confirmation. As for corruption, having the database split with each user having a personal copy of the FE minimizes the potential for corruption but I still wouldn't use Jet/ACE backends on a wireless network. Wireless networks are too prone to "blips" and they can corrupt the BE. If your network is wireless, convert to SQL Server.