Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Create Security Group Account with VBA

Last week we have learned how to create a Microsoft Access Security User Account(SMITHJOHN) with VBA and learned 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 we need to group Users under different categories (like MANAGERS, SUPERVISORS, DATA ENTRY OPERATORS, EDITORS and so on) so that their access rights to different objects can be defined differently at group level and any number of Users can be joined to these groups. Once we clearly define group level access rights all Users joined to that group will inherit the privileges assigned to that Group.

One User can be joined to more than one Group as well. A sample image of the User and Group Accounts control is given below and we will examine how the User SMITHJOHN's Security credentials are appearing in the Control.

In the User Name control SMITHJOHN is selected. The Group Account name Users is appearing under the label Member of indicating that he belongs to that Group Account only. If you look under the Available Group List you can see that the Users Group Account is appearing there also. Unlike the Users Group Account other Group Accounts shown there are created manually for different Group of Users.

If we want to make the User SMITHJOHN as a member of the VEHGRP then all we have to do is to select the VEHGRP under the Available Groups List and click on the Add>> Command Button to copy that name into the Member of: List. Whatever Access Privileges we have assigned to the VEHGRP will be automatically available to the selected User in the User Name control above.

At the end part 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

But, to do that first we must create the Group Account VEHGRP by going through the same procedure like creating a new User Account from the Groups Tab on the Control above. So, the VEHGRP Account was created at some point of time earlier.

Here, we will learn:

  1. How to create a Microsoft Access Security Group Account MANAGER with VBA.
  2. How to join the User SMITHJOHN to the MANAGER Group Account with VBA.

NB: The User and Group Accounts are not case sensitive. Here they are given in Upper Case 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
  1. Copy and Paste the above program into a Standard Module.
  2. Click somewhere in the middle of the Code and press F5 Key to Run the Code and to create the MANAGER Group Account.
  3. You may select Tools - -> Security - -> User and Group Accounts . . . (from the Database Window).
  4. Select SMITHJOHN in the User Name List.
  5. Check for the name MANAGER in the Available Groups List and you will find it there.

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

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 MANAGER group name is now appearing under the Member of List indicating that the User is a member of the MANAGER Group Account.

See the sample image given below.

Share:

1 comment:

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captur...

Labels

Blog Archive

Recent Posts