4

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?

atroon
  • 498
  • 3
  • 10
  • 23
  • I should add that I want to use AD group memberships and OUs because we have a fully functional AD system in place, and that controls access to files, intranet, Jabber, and email. Switching to AD would eliminate an additional password for users to remember and for me to set up. – atroon Jan 21 '10 at 21:48

2 Answers2

4

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).

Community
  • 1
David W. Fenton
  • 232
  • 1
  • 7
  • As David might recall I'm one of the few. Yes, I do have all the necessary code working but it took me about three days to figure out what code I needed and another two days to code it. – Tony Toews Feb 19 '10 at 22:05
1

It's not possible to directly interface with AD at that level. Best you'd be able to do directly is assign file permissions based on AD accounts. It'd take a bit of effort to pull it off through VBA, but certainly not out of the reach of comprehension. I'd say you should do a pretty solid ROI analysis on it before tackling.

squillman
  • 37,618
  • 10
  • 90
  • 145
  • 2
    This is wrong. Of course it's possible to get the info. from AD. It's also possible to get group membership with API calls. -1 for an incorrect answer. – David W. Fenton Jan 21 '10 at 19:34
  • @David, but from within Access, it isn't the easiest thing to do (but as you said, not impossible) +1... also +1 to the answer simply because as incorrect as some of it is, I would personally use AD file permissions - however, I doubt this will help/solve the actual question simply because they need user/group restrictions within the file... so -1... net 0! – William Hilsum Jan 21 '10 at 19:37
  • 1
    @David: Like I said, it's possible if you code it. It's not possible to set it up through interfaces such as the User Level Security Wizard. I've coded many an AD interface for VBA... I know that's possible and this is what I stated. – squillman Jan 21 '10 at 21:14
  • The original question mentions APIs for interfacing with AD, so I think the original question was not for a point-and-click or macro-based solution. Perhaps you could edit your answer to explicitly define "at that level," which seems to be the point of dispute here. – David W. Fenton Jan 22 '10 at 22:48