Do an action if a user is in a usergroup

[flaviooooo]

Hi,

I don't know if this is possible, but here I go:

I have my users divided in different user groups. Some have access to database, others don't... Simple as that.

Now I would like to trigger an update query from one database in another. So if one of our users would open a form in database 1, an update-query would run updating data in database2.

The problem is that some people that have access to db1, can't open db2.
So I would like to create some kind of IF-statement that would check if the user opening the form, is in a certain user group. Else the update-query would not run.

I hope this makes sense.

Thanks in advance

Kind regards,
Flavio

 

[stix4t2]

Can you handle this through error checking, just skip if not possible. If you want to run the query whether they have access or not, open a connection to db2 using different permissions to allow for the update.

 

[Zion7]

Here's maybe a start?

Sub UsersI()
Dim usr As User
For Each usr In Application.DefaultWorkspaceClone.Users
Debug.Print usr.Name
Next
End Sub

If Application.DefaultWorkspaceClone.Users("JaneDoe") = Environ("UserName") Then

 

[easyit]

Hi,

It is not clear if the user groups you mention are msaccess groups or operating system groups.

If the latter, then...I don't think it is possible, since your OS blocks access.
If the first, then you can use ado in VBA to enable what you want:

Public Function BelongsToGroup(strUserName As String, strGroupName As String) As Boolean
' This function will return TRUE IF the current user is a member of the group supplied in the arguments _
Dim conn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim i, k As Integer
Dim ConnStr As String
On Error GoTo Error
BelongsToGroup = False
MdwLocation = "Jet OLEDB:System database=" & SysCmd(acSysCmdGetWorkgroupFile)
ConnStr = "data source=" & CurrentDb.name & ";" & MdwLocation & ";user id=db_admin;Password='mypassword'"

With conn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = ConnStr
    .Open
End With

Set cat.ActiveConnection = conn
With cat

For i = 0 To (.Users(strUserName).Groups.Count - 1) ' Check all groups of this username
'Debug.Print "gebruiker: " & strUserName & " behoort tot de groep: " & .Users(strUserName).Groups(i)
    If .Users(strUserName).Groups(i) = strGroupName Then
        BelongsToGroup = True
    End If
Next i
End With

conn.Close
Exit Function

Error:
If Err.Number = 3265 Then
    'Debug.Print "gebruiker: " & strUserName & " is geen gebruiker van deze database."
    Exit Function
Else
    DoCmd.SetWarnings True
    MsgBox Err.Description & " " & Err.Number
End If

conn.Close
End Function

As you can see, the connection string contains a user (and password) with the appropriate rights. Of course, this user must exist. Also, since the user name & password are visible in VBA, you should distribute your accessdb as mde files.

EasyIT