Creating a User Account with VBA.
To create a Microsoft Access user account in the Workgroup Information File '.mdw', navigate to:
Tools → Security → User and Group Accounts → User Name tab.
There, provide a unique User Name and a Personal ID to create a new user account.
We discussed this procedure earlier in the Security Main Menu. The User IDs, Personal IDs, Workgroups, and Passwords are stored in the Workgroup Information File, a database with the '.mdw' extension. The default file used by Microsoft Access is SYSTEM.MDW.
Access privileges for database objects are stored within the database itself. Together, these two components form the combination lock of Access security:
-
The User ID and Personal ID stored in the Workgroup Information File act as the key, and
-
The object-level access rights stored within the database serve as the lock.
A user gains access to specific objects only when the correct User ID, Password, and Personal ID are provided.
The Personal ID is a critical part of each user’s profile—it is required if you ever need to recreate a Workgroup Information File after the original one becomes corrupted or lost.
Only Administrators (members of the Admins Group with administrative privileges) have the authority to create and manage User and Group IDs.
Creating a User Account Manually.
First, let’s create a test user account manually. Follow the procedure given below to create a user account step by step.
User Name: JOHNSMITH (maximum 20 characters)
- Personal ID: JS123456 (4 to 20 Alphanumeric characters)
Visit the link: Create MS-Access User Account for more details.
Select Tools -> Security -> User and Group Accounts
Click New in the Users Tab.
Type JOHNSMITH in the Name control.
Type JS123456 in the Personal ID control.
Click OK to complete the procedure.
The Users Group.
By default, all User Accounts are added to the Users Group Members. If a user needs to belong to additional user groups, you must select the desired group name from the Available Groups list and add it to the Member Of list using the control on the right-hand side.
Now, let’s learn how to create a user account using VBA—excluding, for now, the step that assigns the user to a specific group. We’ll explore how to link users to groups later, after we cover the topic: how 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..! "
Err.Clear
Exit Function
End If
wsp.Users.Refresh
'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
.Users.Refresh
End With
End FunctionWhen we create a User ID, we can set a password manually for the User. 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.
Copy and paste the above Code into a Standard Module and save it.
Click in the middle of the Code and press F5 to run the Code and create the User Account in the active Workgroup Information File.
Select Tools - ->Security- ->User and Group Accounts and check for the User Name SMITHJOHN on the list.
The User Names are in alphabetical order.
Check for a Particular UserName with VBA.
We can check for the presence of a particular User Name 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
Next
End With
If flag Then
MsgBox "User Name: " & strUsrName & " Already Exists! "
End If
Check4UserAccount = flag
End FunctionType the following in the Debug Window (Immediate Window) and press the Enter Key to run the above Code:
Check4UserAccount "SMITHJOHN"
If you have not run the first program to create the User Account SMITHJOHN, 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:
