Check if user is member of an Active Directory group with access vba

Skip to first unread message

Roady Mayhem

Feb 18, 2016, 4:07:13 AM2/18/16
Hi all,

I am currently building a database in Access 2010. At the moment the database is opened a log-in appears, asking for username and password.

These are checked with the data in Active Directory.

The unique username is then in vba lookup in a query and 'user' and 'role' are defined. These are public variables, remaining there value throughout the session.

Private Sub cmdLogin_Click()

myuser = Me.txtuser.Value
mypwd = Me.txtpwd.Value

recognized = Authenticated(myuser, mypwd)

If recognized = True Then

role = DLookup("Job", "qryUserNameCheck", "Windowsname = '" & Me.txtuser.Value & "'")
user = DLookup("SCMID", "qryUserNameCheck", "Windowsname = '" & Me.txtuser.Value & "'")

DoCmd.Close acForm, "frmAuthenticUserLogIn"
DoCmd.OpenForm "MainInterface"
Exit Sub

MsgBox "Not recognized.", vbOKOnly, "Sorry"
Me.txtpwd.Value = ""
Me.txtuser.Value = ""

If Logintries = 3 Then
DoCmd.Quit acQuitSaveNone
End If

End If

End Sub
The function Authenticated checks the Active directory for windows username and windows password.

Function Authenticated(strUserID As String, strPassword As String, Optional strDNSDomain As String = "") As Boolean

If strDNSDomain = "" Then
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
End If

Set dso = GetObject("LDAP:")
On Error Resume Next
Set ou = dso.OpenDSObject("LDAP://" & strDNSDomain, strUserID, strPassword, 1)
Authenticated = (Err.Number = 0)

End Function
I found this function on the internet and works like a charm.

As you can see, the role of the logged in user is defined by the field Job in the query qryUsernameCheck.
This also works as it should.
But, Quality Assessment demands that the role of the user in the database is defined by the question if the user is a member of a group in Active Directory.
So, we asked IT to add a few groups on active directory (project-adm, project-mgt, project-wfl) and also add a few users to each group.
Now, my question is: how do i check with vba if a user is a member of one of these groups, so that the role in the database is defined.

Any help is greatly appreciated.

Sybolt | |

Jan 11, 2021, 5:40:08 AM1/11/21
SQL server has this natively bro. and SQL Server is free if you use the Express Edition. I can help you, but you don't need to recreate the wheel
Reply all
Reply to author
0 new messages