Deleting a user from the workgroup

[Samulayo]

I'm using the MS access security system and I was wondering if anyone knew of a way to create a form to delete a user from a workgroup or just move him to a no access group.
Thanks to some direction from JeremyNYC I am able to add a user using the CUser function from Microsoft and assumed this could be modified to do the reverse, but as yet cannot find this documented anywhere.

Regards

Samulayo

 

[Mikeauz]

Hi,

Was looking for the same thing and found the following code

Username = cboUsername 'Combo box on form'
DBEngine(0).Users.Delete (Username)

Works great in Access 2000

Hope it helps

 

[Ed2020]

'Contains procedures to manage MS Access security via VB.
'Uses Jet 4 ANSI-92 extensions accessible via SQL so will only work in
'MS Access 2000 or later.
'Also have to use ADO connections to run the SQL.
'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.....

 

[AKMonkeyboy]

I'm attempting to use the RemoveFromGroup function and am getting the message "expected user or group name" - any ideas?

I have the variables set up on a form...

A

 

[Ed2020]

Sounds like the user and/or group name you're passing is incorrect, but I'm not sure.

Ed.

Please do not feed the trolls.....