• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Code to manage system.mdw file

I have an MS Access 2000 database which I have used Microsoft Access security to secure and am using an .mde file to get to the backend access database. It is working good. But whenever I add users or modify security permissions of the users, I have to use the Tools->Security and the appropriate option.

I was wondering if there was some code available either in Access Basic or Visual Basic which could provide with an interface to do this i.e. manage users and security for the database.
1 Solution
You will need to add a reference to "Microsoft ADO Ext. 2.x for DDL and Security" where 2.x is the version of ADo you are using.

Assuming you have a ADO connection to the database called AdoCN:

Dim AdoxCat As New ADOX.Catalog
Set AdoxCat.ActiveConnection = TimeCn

On Error Resume Next

'Add user
AdoxCat .Users.Append (TxtUserName.Text)
if Err.Number>0 then 'Not sure what the correct error number is
  'User already exists. Handle here.
end if

'Change users password - if you are an administrator
AdoxCat .Users(TxtUserName.Text).ChangePassword "", TxtNewPass.Text
if Err.Number>0 then 'Not sure what the correct error number is
  'User doesn't exists. Handle here.
end if

'Change your own password
AdoxCat .Users(TxtUserName.Text).ChangePassword TxtOldPass.Text, TxtNewPass.Text
if Err.Number>0 then 'Not sure what the correct error number is
  'Incorrect password. Handle here.
end if

'Add user to an existing security group:
AdoxCat.Groups(TxtSecurityGroup.Text).Users.Append TxtUserName.Text
if Err.Number=3265 Then
  'User doesn't exist. Handle here.
Elseif Err.Number=-2147467259
  'User Already Exists in this group - so can continue
  'Unknown error. Handle here
End If

If you want to grant permissions to objects for a security group you will need to use the SetPermissions Method. I've never used this, but if you look in the ADO help file for SetPermissions you will find details of how to use this.

Basically the call is:
GroupOrUser.SetPermissions Name, ObjectType, Action, Rights [, Inherit] [, ObjectTypeId]

Where name is the name of the object (table/view/column etc).
Object type is one of the object constants (starting adPermObj. Intelli Type will give you the list - fairly obvious what they mean)
Action type is one of the action constants (starting adAccess)
Rights is one of the rights constants (starting adRight)
Inherit is one of the inherit constants (starting adInherit)

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now