Can I add a user to Access with VBA, instead of using the builtin User and Group Account box?

Question

While making an access database, I'd like to make it as idiot proof as possible. This means, I don't want the client to have to use the access components; I'd rather have my own form that just takes a username and password and adds it to the correct groups automatically.

I thought I had some code that would work:

Dim usr as User 
set usr = new User

usr.Name="Foo"
'set other properties'
DBEngine.Workspace(0).Users.Append(usr)

but it tells me that the operation is not supported. Is there any other way to get a new user inserted into the security file?

Answer 1

Which access version are you using. Are you just trying to add a new user to a group/new group.

I found this example on Access 2003, and it seemed to work just fine

Sub CreateUserX(ByRef strPassword As String)

   Dim wrkDefault As Workspace
   Dim usrNew As user
   Dim grpNew As Group
   Dim usrTemp As user
   Dim prpLoop As Property
   Dim grpLoop As Group

   Set wrkDefault = DBEngine.Workspaces(0)

   With wrkDefault

      ' Create and append new User.
      Set usrNew = .CreateUser("NewUser")
      usrNew.PID = "AAA123456789"
      usrNew.Password = strPassword
      .Users.Append usrNew

      ' Create and append new Group.
      Set grpNew = .CreateGroup("NewGroup", _
         "AAA123456789")
      .Groups.Append grpNew

      ' Make the user "NewUser" a member of the
      ' group "NewGroup" by creating and adding the
      ' appropriate User object to the group's Users
      ' collection.
      Set usrTemp = _
         .Groups("NewGroup").CreateUser("NewUser")
      .Groups("NewGroup").Users.Append usrTemp

      Debug.Print "Properties of " & usrNew.Name

      ' Enumerate the Properties collection of NewUser. The
      ' PID property is not readable.
      For Each prpLoop In usrNew.Properties
         On Error Resume Next
         If prpLoop <> "" Then Debug.Print "  " & _
            prpLoop.Name & " = " & prpLoop
         On Error GoTo 0
      Next prpLoop

      Debug.Print "Groups collection of " & usrNew.Name

      ' Enumerate the Groups collection of NewUser.
      For Each grpLoop In usrNew.Groups
         Debug.Print "  " & _
            grpLoop.Name
      Next grpLoop

      ' Delete the new User and Group objects because this
      ' is a demonstration.
      .Users.Delete "NewUser"
      .Groups.Delete "NewGroup"

   End With

End Sub

Will that be helpfull?

Answer 2

Use DDL to create user "fred" with password "pword":

CurrentProject.Connection.Execute "CREATE USER fred pword;"

Add fred to Users and Admins groups:

CurrentProject.Connection.Execute "ADD USER fred TO Users;"
CurrentProject.Connection.Execute "ADD USER fred TO Admins;"

MSDN documentation for Data Definition Language here: http://msdn.microsoft.com/en-us/library/bb267262.aspx

You can use "ALTER USER ..." to change password, and "DROP USER ..." to delete the user.

Answer 3

After much hair pulling and an epic battle against lacking documentation, here it is. You must use ADOX. Link it in in the references menu in the VBA editor.

Dim cat as ADOX.Catalog
Dim user as ADOX.User

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

usr = new ADOX.User
usr.Name = "joe"
cat.Users.Append usr

' must change password on user after inserted '
cat.Users("joe").ChangePassword "", "pass"
cat.Users("joe").Groups.Append "Users" ' have to be in this to open database '
cat.Users("joe").Groups.Append "MyCustomGroup"

Set cat = Nothing
Set usr = Nothing

• There are other Objects that have connection objects. They do not work. CurrentProject.Connection is the only one that worked for me.
• Some documentations show a change password on the user object before appending it to the Users catalog. That did not work right.
• The various documentations seemed to show that the Users Collection could do this, but it could not append - it needs the ADOX Catalog for that.