Code to manage system.mdw file

Posted on 2005-04-14
Last Modified: 2013-12-03
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.
Question by:thenosedoctor
    1 Comment
    LVL 10

    Accepted 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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now