Solved: How do I tap into User Level Security with VBA?

[Hathman]

I have searched long and hard to find the code necessary to tap into the User Level Security functionality and can't seem to find it. I'm sure there's a way, it's just a matter of figuring it out. I basically want to add new users and groups as well as modify permissions to objects via my own front end rather than using MS Access built-in User Level Security screens.

Has anyone done this before?

Thanks,
Hathman

[hansup]

You can use VBA to modify User Level Security, but I find it more convenient to use DDL. For example, 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. You can use "GRANT ..." and "REVOKE ..." statements to modify permissions for database objects.

If you prefer to do it with VBA instead, see this discussion on StackOverflow: http://stackoverflow.com/questions/1480306/can-i-add-a-user-to-access-with-vba-instead-of-using-the-builtin-user-and-group

[Hathman]

That's great hansup! I'll construct my code and see how well it works. I appreciate the quick response!

Thanks so much,
Hathman

:vv

[Hathman]

Just wanted to confirm back that it worked perfectly! Thanks so much!

[hansup]

You're welcome. Glad it was useful.

[volkerpeter]

I hve no idea where to write these DDL commands , can anyone help? I am new to access 2007. I just created a simple database with some tables and forms , and want tp provide user level access to forms . is it possible ?
THanks

[hansup]

You mentioned Access 2007. User level security is not available with the new ACCDB database format. If you want ULS, you can create you database in the older MDB format and Access 2007 can use it.

If your database is simple, why do you even want to use code to administer users and their permissions? It could be simpler to maintain them through the user interface. You won't see the option on the ribbon if your database is ACCDB. But if you open an MDB in 2007, the option will be displayed on the ribbon.

[volkerpeter]

alright , thanks for ur response. I didn't knew that , but i will try this option.

Peter

[Hathman]

Here's what I eventually added to my database. This code is attached to a button to create new users. It grabs their network ID, adds them as a user and then adds them to specific groups (Users and AppUsers).

If UserExists(Me!txtNetworkID) = False Then
CurrentProject.Connection.Execute "CREATE USER " & Me!txtNetworkID & " " & Me!txtNetworkID & ";"
CurrentProject.Connection.Execute "ADD USER " & Me!txtNetworkID & " TO Users;"
CurrentProject.Connection.Execute "ADD USER " & Me!txtNetworkID & " TO AppUsers;"
End If


Here's the code for the "UserExists" function:
Public Function UserExists(InUser As String) As Boolean
Dim wks As Workspace
Dim Usr As USER
Set wks = DBEngine.Workspaces(0)
With wks
'Compare all users within the workgroup with the current user
For Each Usr In .Users
'If the current user = user found within workgroup then verify to what group this user belong
If Trim(InUser) = Usr.Name Then
UserExists = True
Exit Function
Else
UserExists = False
End If

Next Usr

End With
End Function