Create Security Group Account with VBA.
Last week, we learned how to create a Microsoft Access Security User Account (SMITHJOHN) with VBA and how to join him as a member of the default Group Account Users. The Users and Admins Group Accounts are already present in the default Workgroup Information File (Sytem.mdw ) or in the new Workgroup Information File that you create separately.
If Users need to be organized into categories such as Managers, Supervisors, Data Entry Operators, Editors, and others, access rights can be defined at the group level instead of for individual Users. Any number of Users can be assigned to a group. Once the access rights for a group are defined, every User assigned to that group automatically inherits the privileges granted to it.
One User can be joined to more than one Group as well. A sample image of the User and Group Accounts control image is shown below, and we will examine how the User SMITHJOHN's Security credentials appear in the Control.
In the User Name control, SMITHJOHN is selected. Under the Member label, the Users Group Account is displayed, indicating that this user currently belongs only to that group. The Users Group Account also appears in the Available Group List. Unlike the Users Group Account, which is the default group, the other Group Accounts listed under the Available Group List have been created manually to organize different categories of Users.
To make the user SMITHJOHN a member of the VEHGRP Group Account, select VEHGRP from the Available Groups list and click the Add >> Command Button. This action copies the group name to the Member list. Once added, the selected user automatically inherits all the Access Privileges assigned to the VEHGRP Group Account.
At the end of last week's main program, CreateUser, the following code segment is doing exactly what we did manually in the above paragraph:
. . .
With wsp
Set tempGrp = .Users(UsrName).CreateGroup("Users")
.Users(UsrName).Groups.Append tempGrp
.Users.Refresh
End With
Creating the Group Account.
Create the Group Account VEHGRP by going through the same procedure as creating a new User Account from the Groups Tab on the Control above. So, the VEHGRP Account was created at some point in time earlier.
Here, we will learn:
- How to create a Microsoft Access Security Group Account MANAGER with VBA.
- How to join the User SMITHJOHN to the MANAGER Group Account with VBA.
NB: User and Group Accounts are not case-sensitive. Here they are given in uppercase for legibility only.
The following Program creates the MANAGER Group Account in the Available Groups List:
Public Function CreateUserGrp()
'---------------------------------------------------------------------
'Creating a Security Group Account
'Author : a.p.r. pillai
'Date : March-2010
'All Rights Reserved by www.msaccesstips.com
'---------------------------------------------------------------------
Dim newGrp As Group, wsp As Workspace
Dim tempGrp As Group
Dim grpName As String, grpID As String
On Error Resume Next
Set wsp = DBEngine.Workspaces(0)
grpName = "MANAGER"
grpID = "MGR13579"
With wsp
Set newGrp = .CreateGroup(grpName, grpID)
.Groups.Append newGrp
If Err = 3390 Then
MsgBox "Group Name : " & grpName & vbCr & vbCr & "Group PID : " & grpID & vbCr & vbCr & "Account Name already Exists..! "
Err.Clear
End If
.Groups.Refresh
End With
End Function- Copy and paste the above program into a Standard Module.
- Click somewhere in the middle of the Code and press the F5 Key to run the Code and create the MANAGER Group Account.
- You may select Tools -> Security -> User and Group Accounts (from the Database Window).
- Select SMITHJOHN in the User Name List.
- Check for the name MANAGER in the Available Groups List, and you will find it there.
Adding the User Account to a Group.
But the User SMITHJOHN is not yet added to the MANAGER Group Account, and this is where we need to add the code segment given at the top of this page to the main program.
The revised VBA Code is given below to create the MANAGER Group Account and to add the User SMITHJOHN to this Group Account.
Public Function CreateUserGrp()
'---------------------------------------------------------------------
'Creating a Security Group Account
'Author : a.p.r. pillai
'Date : March-2010
'All Rights Reserved by www.msaccesstips.com
'---------------------------------------------------------------------
Dim newGrp As Group, wsp As Workspace
Dim tempGrp As Group
Dim grpName As String, grpID As String
On Error Resume Next
Set wsp = DBEngine.Workspaces(0)
grpName = "MANAGER"
grpID = "MGR13579"
With wsp
Set newGrp = .CreateGroup(grpName, grpID)
.Groups.Append newGrp
If Err = 3390 Then
MsgBox "Group Name : " & grpName & vbCr & vbCr & "Group PID : " & grpID & vbCr & vbCr & "Account Name already Exists..! "
Err.Clear
End If
.Groups.Refresh
End With
'Add the User SMITHJOHN to the MANAGER Group Account
usrName = "SMITHJOHN"
With wsp
Set tempGrp = .Users(usrName).CreateGroup(grpName)
.Users(usrName).Groups.Append tempGrp
.Users.Refresh
End With
End Function
Running the Code a Second Time.
If you run the revised Code again, it will show an Error Message saying that the MANAGER Group Account already exists, because you have already run this code once. But the remaining part of the code will run.
If you open the User and Group Accounts control now and select SMITHJOHN in the User Name Control, you can see that the MANAGER group name is now appearing under the Member List, indicating that the User is a member of the MANAGER Group Account.
See the sample image given below.
Thank you Claudia, I will check it out.
ReplyDeleteRegards,