My company makes fairly extensive use of an Access + MySQL application that would probably see some significant traffic on the Daily WTF if I posted the source code. The management of users and their permissions is getting out of hand, and I seem to spend more and more time dealing with tweaking these or trying to figure out why someone can't see what they're supposed to see.
It was originally set up to be used by three users in one warehouse. It's now used by over twenty users in four states, with more to be added soon, and the features have been added in a roughly 10-to-1 ratio with the users… The actual core application isn't bad, but managing users is a pain. Access makes a nice front end to the data itself, which is stored on a MySQL backend in our head office. Users have Cisco VPN boxes at satellite branches, and that's been solid as well. Scope has crept from a simple warehouse shipping record to a full-fledged CRM/ERP …well, I don't suppose you could call this a solution. An emulsion, maybe. If I had the budget, I'd call up SAP and tell them to have at it. That, I'm afraid, is out of the realm of possibility for the foreseeable future.
Following instructions from Google (not always the safest thing to do) I used the 'User-Level Security Wizard' in Access to assign usernames and passwords to various users, which was fine when I started with 4-5 users total and 3 active users. But it's now quite unwieldy. My deepest wish and desire is that there would be some way to authenticate users and assign privilege roles based on Active Directory username and password. I'm told that's impossible. A few Google searches have turned up nothing of note.
I surmise that it should be possible to get some sort of authentication framework using Active Directory because VBA has links to all manner of APIs in Windows. However…is it worth the time and trouble? Has anyone ever gotten this to work, or am I liable to blow up not only my WTF-worthy application but the domain as well?
Best Answer
I know it's possible to do, but very few Access developers seem to be doing it. If somebody else wrote the code, I'd use it myself, but don't need it enough to write it myself.
The key concept is that you can access AD information via an LDAP query using ADO. There's no way to enforce permissions on Access objects with that, but you could certainly control application flow/presentation based on AD membership. See this thread for a starting point. Also, there's an MS Knowledge Base article on this that explains the LDAP approach.
BTW, as long as you don't need AD-specific functionality (such as organizational units), you don't need to use AD at all. You can use regular API calls to get group membership information. See this Stackoverflow post for some code suggesting the direction to go (I can't verify that code, as it looks rather elliptical, i.e., not API declarations, but it gives the basic concept).