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?