Can I add a user to access VBA instead of using the built-in user and group account?
When creating an access database, I would like to do this as idiot proof as possible. This means that I don't want the client to have to use the accessor components; I would rather have my own form that just takes the username and password and automatically adds it to the correct groups.
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 he tells me the operation is not supported. Is there any other way for the new user to insert into the security file?
source to share
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: http://msdn.microsoft.com/en-us/library/bb267262.aspx
You can use "ALTER USER ..." to change the password and "DROP USER ..." to delete the user.
source to share
What version of access are you using. You are just trying to add a new user to the group / new group.
I found this example in 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 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 this be helpful?
source to share
This MSDN article describes what you are trying to do:
http://msdn.microsoft.com/en-us/library/aa190108(v=office.10).aspx
Ironically, the other three answers cover one of the approaches: DDL, DAO, and ADOX.
source to share
After a lot of hair pulling and an epic battle with missing documentation, here it comes. You must use ADOX. Link it to the link 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 documents specify a change password for a custom object before adding it to the Users directory. It didn't work.
- Various documents seemed to show that a collection of users can do this, but it cannot be added - it needs the ADOX directory.
source to share