User Administration via VBA

[KornGeek]

I'm trying to locate the functions for creating user accounts, deleting user accounts, adding users to groups, and removing users from groups.

I know I've found this code before, but I've been searching and can't seem to locate it again.

I appreciate any guidance you might offer.

 

[AlexMidd]

I assume you are thnking of the Group and Users collections within the Access object model. Do a search for these in the VBA help and this should point you in the right direction.

Have fun! )

Alex Middleton

 

[psemianonymous]

Of course you can choose the ADO code or the DAO code--that's part of the problem. One of the really nasty parts of using the DAO code reliably (as I recall) was that you have to provide an admin username & password to "create a new workspace" so you could manipulate the users and groups. It was nasty. I never tried the ADO method so I can't speak for its relative ease/gotchas.

Anyway, comp.databases.ms-access is (as always) the best resource:

http://groups.google.com/group/comp...es.ms-access&q=adding+users+to+a+group&qt_g=1

 

[JerryKlmns]

Or Managing Security with Jet DDL statements against a connection!

Cnxn.Execute "DROP DROP GROUP Billing

 

[Ed2020]

Give the following a try if you are using Access 2000 or later:

'Contains procedures to manage MS Access security via VB.
'Uses Jet 4 ANSI-92 extensions (for simplicity) accessible via SQL so will only work in
'MS Access 2000 or later.
'Also have to use ADO connections to run the SQL. CurrentDB.Execute runs in Jet3.6...
'Written by Ed Metcalfe, 28/11/2002.

Public Sub CreateUser(ByVal strUserName As String, ByVal strPWord As String, ByVal lngPID As Long)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "CREATE USER " & strUserName & " " & strPWord & " " & lngPID & ";"

'All users must be members of users group
Call AddToGroup(strUserName, "Users")

cnn.Close
Set cnn = Nothing

End Sub

Public Sub CreateGroup(ByVal strGroupName As String, ByVal lngGID As Long)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "CREATE GROUP " & strGroupName & " " & lngGID & ";"

cnn.Close
Set cnn = Nothing

End Sub

Public Sub ChangePass(ByVal strUserName As String, strNewPass As String, strOldPass As String)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "ALTER USER " & strUserName & " PASSWORD " & strNewPass & " " & strOldPass & ";"

cnn.Close
Set cnn = Nothing

End Sub

Public Sub AddToGroup(ByVal strUserName As String, ByVal strGroupName As String)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "ADD USER " & strUserName & " TO " & strGroupName & ";"

cnn.Close
Set cnn = Nothing

End Sub

Public Sub RemoveFromGroup(ByVal strUserName As String, ByVal strGroupName As String)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "DROP USER " & strUserName & " FROM " & strGroupName

cnn.Close
Set cnn = Nothing

End Sub

Public Sub DeleteUser(ByVal strUserName As String)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "DROP USER " & strUserName & ";"

cnn.Close
Set cnn = Nothing

End Sub

Public Sub DeleteGroup(ByVal strGroupName As String)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "DROP GROUP " & strGroupName & ";"

cnn.Close
Set cnn = Nothing

End Sub

Public Sub AssignPermission(ByVal strAction As String, ByVal strPermissionName As String, ByVal strObjectName As String, ByVal strObjectType As String, ByVal strAccountName As String)
'strAction - GRANT Or REVOKE
'strPermissionName - SELECT, DELETE, INSERT, UPDATE, DROP, SELECTSECURITY, UPDATESECURITY,
'DBPASSWORD, UPDATEIDENTITY, CREATE, SELECTSCHEMA, SCHEMA, UPDATEOWNER
'strObject Name - Name of table/form/report etc.
'strObjectType - "TABLE", "OBJECT" OR "CONTAINER"
'strAccountName - name of user or group
'How do you give design privs then??

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

Select Case strAction
Case "GRANT"
cnn.Execute "GRANT " & strPermissionName & " ON " & strObjectType & " " & strObjectName & " TO " & strAccountName & ";"
Case "REVOKE"
cnn.Execute "REVOKE " & strPermissionName & " ON " & strObjectType & " " & strObjectName & " FROM " & strAccountName & ";"
End Select

cnn.Close
Set cnn = Nothing

End Sub

Please do not feed the trolls.....