Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Creating User-Account with VBA


For creating a Microsoft Access User-Account in the WorkGroup Information File (mdw), we go through Tools - -> Security - -> User and Group Accounts - ->User Name Option. There, we need to provide a unique User Name and a Personal ID to create a User Account.

We have seen this procedure in the Security Main Menu. The User IDs, Personal IDs, Workgroups, and Passwords of Users are maintained in the Workgroup Information File (a database with the mdw extension and the Default Workgroup Information File is System.mdw).

Access privileges to Database Objects are stored within the database itself. Both these two parts of the Combination-Lock (User ID with Personal ID in Workgroup Information File and access rights to individual objects for each User/Group maintained within the database) work together. The individual User gets access to Objects with the right UserID, Password, and Personal ID. Personal Id is a very important element of the User-Profile and we need it again if we ever need to recreate a Workgroup Information File when the existing one is corrupted or lost.

User IDs and Group IDs can be created and managed only by Administrators (a member of the Admins Group with Administrator privileges) of the Database.

Creating a User Account Manually.

First, let us create a test User Account manually. Go through the following procedure for creating a User Account manually, and see how we can do it.

User Name: JOHNSMITH (maximum 20 characters)

  • Personal ID: JS123456 (4 to 20 Alphanumeric characters)

Visit the Page: Create MS-Access User-Account for more details.

  1. Select Tools - -> Security - -> User and Group Accounts
  2. Click New in the Users Tab.
  3. Type JOHNSMITH in the Name control.
  4. Type JS123456 in Personal ID control.
  5. Click OK to complete the procedure.

The Users Group.

By default, all User Accounts will be members of the Users Group. If the User is going to be part of any other User-Groups then that Group Name must be selected from the Available Groups list and Add it to the Member of the list on the right-hand side control.

Let us see how to create a User-Account with VBA, except for the procedure explained for joining the User to a particular Group. We will examine the procedure for joining the User to a Group later, after learning to create a Group Account with VBA.

Create a User Account with VBA.

The following VBA Code creates a User Account with the User Name: SMITHJOHN, with the Personal ID: SJ78901 and with an initial Password: SMITHJOHN:

Public Function CreateUsers()
'Author : a.p.r. pillai
'Date   : Feb-2010
'All Rights Reserved by www.msaccesstips.com
'Other Ref: http://www.msaccesstips.com/2006/11/create-msaccess-user-account/
Dim wsp As Workspace
Dim newUser As User, tempGrp As Group
Dim UsrName As String, PersonalID As String
Dim Password As String

On Error Resume Next

Set wsp = DBEngine.Workspaces(0)

    UsrName = "SMITHJOHN" ' 1 to 20 characters.
    PersonalID = "SJ78901" 'upto 4 to 20 alphaumeric characters
    Password = "SMITHJOHN" 'upto 14 characters except NULL

'Create User Account with personalID
   Set newUser = wsp.CreateUser(UsrName, PersonalID, Password)
   wsp.Users.Append newUser

   If Err = 3390 Then
      MsgBox "User Name : " & UsrName & vbCr & vbCr & "User PID    : " & PersonalID & vbCr & vbCr & "Account Name already Exists..! "
      Exit Function
   End If

'Users Group must be created and joined the
'User to it. When created manually this step
'(for Users Group)is done automatically.
   With wsp
     Set tempGrp = .Users(UsrName).CreateGroup("Users")
         .Users(UsrName).Groups.Append tempGrp
   End With

End Function

When we create a User-ID we can only set a password for the User manually.  After opening a new MS-Access Application window, select the Menu Options Tools- ->Security- ->User and Group Accounts - - >, log in without a password, select the Change Logon Password Tab of the Dialog Control, and set a new password.

  1. Copy and Paste the above Code into a Standard Module and save it.
  2. Click somewhere in the middle of the Code and press F5 to Run the Code and create the User Account in the active Workgroup Information File.
  3. Select Tools - ->Security- ->User and Group Accounts and check for the User Name SMITHJOHN on the list.

The UserNames are in alphabetical order.

Check for a Particular UserName with VBA.

We can check for the presence of a particular UserName in the active Workgroup Information File with the following VBA Code:

Public Function Check4UserAccount(ByVal strUsrName As String) As Boolean
'Author : a.p.r. pillai
'Date   : Feb-2010
'All Rights Reserved by www.msaccesstips.com

Dim WS As Workspace, msg As String
Dim UsrName As String, K As User, Flag As Boolean

Set WS = DBEngine.Workspaces(0)

'All the Users belong to the Common Group 'Users'
Flag = False
With WS.Groups("Users")
        For Each K In .Users
                  If K.Name = strUsrName Then
                          Flag = True
                          Exit For
                  End If
End With

If flag Then
     MsgBox "User Name: " & strUsrName & " Already Exists! "
End If

Check4UserAccount = flag

End Function

Type the following in the Debug Window (Immediate Window) and press Enter Key to run the above Code:

Check4UserAccount "SMITHJOHN"

If you have not run the first program to create the User Account SMITHJON then to test the second Program, Call the above function with the Admin User Name.

Type the following in the Debug Window (Immediate Window) and press Enter Key:

Check4UserAccount "Admin"

Earlier Post Link References:



  1. This is just what I am looking for.
    However, this does not seem to work for Access2000.
    I'm just a beginner and have no idea about workspace, etc.
    I get the error "User-defined type not defined"??

  2. OK, I did find out that I needed to have an additional reference. Under Tools-Reference I need to have checked;
    "Microsoft DAO 3.6 Object Library"
    All is good after that.
    Now, I wonder if the user that runs my Db must do likewise. I presume so.
    Which raises the question: Can this be accomplished by VBA code without the DAO reference?
    Any further advice/help is appreciated.
    This site is a TREMENDOUS help to me...thanx

  3. Check this Link for Reference Lirary Management:



Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


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