Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Unanswered: Access 2007 Security

    How do you develop user and/or group level security for individual objects (tables, queries, forms) in Access 2007? Since this function is not available as in previous versions, what is the schema that Microsoft suggests for handling individual access to specific objects in a 2007 database?

    Thanks in advance.

    TrainingGuru
    3/25/2010

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    self made style user level "security" - typically involves a table for permissions...lets say at 3 levels. you populate this table with everyone that will log in and assign them a permission level 1,2 or 3.

    then you design the db such that depending on their permission level - certain sets of objects are enabled or not. Also of course you eliminate visibility to the tables, ribbon - and set up a custom menu bar in the FE too.

    however this is "security" only in a limited sense - it actually is more like "visibility" or "useability". If a serious cracker/hacker concern exists - none of this is adequate. 07 offers the BE/table encryption and overall for the casual user environment where the users are not really intentionally devious everything is fine with table encryption and an FE designed as described here. But if the starting point is serious security - then you need to move to sqlserver - as really that is how MS themselves have differentiated these two product sets.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you want to avoid the hassles of MSAccess security (which I prefer not to use), look at this example: http://www.dbforums.com/6332819-post68.html

    You can easily create a user type security table as the example demonstrates and then have buttons visible/invisible depending on the security level. It's much easier than trying to deal with MSAccess security itself.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Mar 2010
    Posts
    7

    Security through opening an MDW file

    PKStormy,

    Thanks for your reply and it does appear to make a lot of sense. My only problem is that you are a bit advanced for me. I have come up with the idea to use the existing .MDW file and use an autoexec macro to call a module which opens this file. I have some code (please be kind) which I'm using to open the file but there is a problem with the line after the comment "Open File". Could you help with this?

    Sub OpenMDWFile()
    Dim Filter As String, Title As String
    Dim FilterIndex As Integer
    Dim Filename As Variant
    ' File filters
    Filter = "Workgroup Files (*.mdw),*.mdw," & ""
    'Default Filter to *.mdw
    FilterIndex = 1
    ' Set Dialog Caption
    Title = "Select a File to Open"
    ' Select Start Drive & Path
    ChDrive ("c")
    ChDir ("c:\program files\common files\system")
    With Application
    ' Set File Name to selected File
    Filename = "system2K_2.mdw"
    ' Reset Start Drive/Path
    ChDrive (Left("c", 1))
    ChDir ("c:\program files\common files\system")
    End With
    ' Exit on Cancel
    If Filename = False Then
    MsgBox "No file was selected."
    Exit Sub
    End If
    ' Open File
    Workgroup.Open Filename
    End Sub

    I know that this is not as sophisticated as your ()GetUser method (which I'd really like to learn to use as I get more into db development and VBA), but if this quick fix works for my current problem, I can move on to our other problems with migrating 27 2000 databases to Access 2007. Again, please let me know if you can help. Thank you in advance.

  5. #5
    Join Date
    Mar 2010
    Posts
    7

    Opening a file in Access 2007

    PKStormy,

    I found the simple one line code to open the security MDW file using VBA code in Access. Even got it to work. But as you already probably know, this does not resolve my issue of using an existing workgroup security file for a specific or specific Access database(s). The application has to open the MDW file referenced by the database first for the security settings to be in effect. In any case, thank you for all of your help and the example database to which you referenced. My task is to get these databases so people can open and use them in 2007 not to re-engineer them, which using your example would require. I'm going to have to use a "band-aid" to accomplish this which for me is discouraging because it isn't anywhere near an optimal resolution.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by TrainingGuru View Post
    PKStormy,

    Thanks for your reply and it does appear to make a lot of sense. My only problem is that you are a bit advanced for me. I have come up with the idea to use the existing .MDW file and use an autoexec macro to call a module which opens this file. I have some code (please be kind) which I'm using to open the file but there is a problem with the line after the comment "Open File". Could you help with this?

    Sub OpenMDWFile()
    Dim Filter As String, Title As String
    Dim FilterIndex As Integer
    Dim Filename As Variant
    ' File filters
    Filter = "Workgroup Files (*.mdw),*.mdw," & ""
    'Default Filter to *.mdw
    FilterIndex = 1
    ' Set Dialog Caption
    Title = "Select a File to Open"
    ' Select Start Drive & Path
    ChDrive ("c")
    ChDir ("c:\program files\common files\system")
    With Application
    ' Set File Name to selected File
    Filename = "system2K_2.mdw"
    ' Reset Start Drive/Path
    ChDrive (Left("c", 1))
    ChDir ("c:\program files\common files\system")
    End With
    ' Exit on Cancel
    If Filename = False Then
    MsgBox "No file was selected."
    Exit Sub
    End If
    ' Open File
    Workgroup.Open Filename
    End Sub

    I know that this is not as sophisticated as your ()GetUser method (which I'd really like to learn to use as I get more into db development and VBA), but if this quick fix works for my current problem, I can move on to our other problems with migrating 27 2000 databases to Access 2007. Again, please let me know if you can help. Thank you in advance.
    Actually, that code above is a lot more complicated than the getuser method. But if your task is to simply maintain and not revise the database, I might suggest one thing you could easily do which will make your life much easier.

    If a user opens the mdb (and sits in the mdb or mde frontend), the associated *.ldb file that opens will eventually get locked. This means any other user who tries to open the mdb will get the "locked by another user" error and won't be able to open the mdb. To resolve this (and you'll undoubtably encounter it if you have more than 3 consecutive users), you can:

    1. Hunt down each user and have them all close out of the mdb, compact and repair the mdb, and then allow users to get back in. After this happens a few dozen times, you may want to consider option 2 below

    2. With the frontend split from the backend, have your users open the frontend mdb (or mde) via the vb script found here: http://www.dbforums.com/6274786-post19.html. It simply clones the mdb (frontend) with the users name and launches the cloned copy for that user (you'd want the cloning to take place in a separate folder). This will prevent from ever getting the "locked by another user error".

    Otherwise, for maintiaining an mdb, if there's a lot of data entry going on, you may have to occassionally compact/repair the mdb.

    In regards to security, I personally find MSAccess security a lot more difficult to work with versus the example I posted. It's very easy to 'lock' yourself out of a protected MSAccess file (just browse through past posts on this subject within this forum.)
    Last edited by pkstormy; 03-27-10 at 03:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Mar 2009
    Posts
    7

    Fix for Access 2007 user accounts?

    Fix for Access 2007 user accounts?
    I have a db that I HAVE to run on a stand-alone computer. I wrote it in Access 2003 but since Access '07 ignored my inplace groups and permissions, I converted the db to '07.

    As a "fix" I downloaded a db (attached) that uses VBA to simulate security by accounts. My problem is that I don't know how to change the code to incorporate my imported forms in this db to make them secure!

    Any answer that will help me must be very clear...again, I don't know VBA.

    My thanks in advance!
    Attached Files Attached Files

  8. #8
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by TrainingGuru View Post
    PKStormy,

    Thanks for your reply and it does appear to make a lot of sense. My only problem is that you are a bit advanced for me. I have come up with the idea to use the existing .MDW file and use an autoexec macro to call a module which opens this file. I have some code (please be kind) which I'm using to open the file but there is a problem with the line after the comment "Open File". Could you help with this?

    Sub OpenMDWFile()
    Dim Filter As String, Title As String
    Dim FilterIndex As Integer
    Dim Filename As Variant
    ' File filters
    Filter = "Workgroup Files (*.mdw),*.mdw," & ""
    'Default Filter to *.mdw
    FilterIndex = 1
    ' Set Dialog Caption
    Title = "Select a File to Open"
    ' Select Start Drive & Path
    ChDrive ("c")
    ChDir ("c:\program files\common files\system")
    With Application
    ' Set File Name to selected File
    Filename = "system2K_2.mdw"
    ' Reset Start Drive/Path
    ChDrive (Left("c", 1))
    ChDir ("c:\program files\common files\system")
    End With
    ' Exit on Cancel
    If Filename = False Then
    MsgBox "No file was selected."
    Exit Sub
    End If
    ' Open File
    Workgroup.Open Filename
    End Sub

    I know that this is not as sophisticated as your ()GetUser method (which I'd really like to learn to use as I get more into db development and VBA), but if this quick fix works for my current problem, I can move on to our other problems with migrating 27 2000 databases to Access 2007. Again, please let me know if you can help. Thank you in advance.
    To use an .MDW file, you do not open the file, you tell access to use it by 1) joining the work group or 2) use a command line switch when launching Access.

    If you are wanting to use the built-in User Level Security, then I would recommend reading this white paper:

    How I use Microsoft Access User-Level Security
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  9. #9
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by gerald_brasher View Post
    Fix for Access 2007 user accounts?
    I have a db that I HAVE to run on a stand-alone computer. I wrote it in Access 2003 but since Access '07 ignored my inplace groups and permissions, I converted the db to '07.

    As a "fix" I downloaded a db (attached) that uses VBA to simulate security by accounts. My problem is that I don't know how to change the code to incorporate my imported forms in this db to make them secure!

    Any answer that will help me must be very clear...again, I don't know VBA.

    My thanks in advance!
    If you are referring to the built in User Level Security (ULS) in JET, it is support by Access 2007 with a .MDB databases just the same way it was in Proir versions.

    f you are wanting to use the built-in User Level Security, then I would recommend reading this white paper:

    How I use Microsoft Access User-Level Security


    If you are wanting to use the example you downloaded then read the include file README.txt. It has all the instructions and VBA code needed to add to your forms.

    The README.txt file
    With this database it is a simple task to add security levels to an existing DB through VBA code. TO do this import all of your existing DB components into the security DB, and on each of your forms set up in the OnOpen event something like this:

    ################################################## ##########################
    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Err_Form_Open


    If User.AccessID = 1 Then
    'Here list all buttons that should be anabled for Admin only
    Me.cmdAdmin.Enabled = True
    Else
    Me.cmdAdmin.Enabled = False
    End If


    If User.AccessID = >1 Then
    'Here list all buttons that should be enabled for level 2 only
    Me.cmdbutton.Enabled = True
    Else
    Me.cmdbutton.Enabled = False
    End If


    Exit_Form_Open:
    Exit Sub

    Err_Form_Open:
    MsgBox Err.Description
    Me.Visible = True
    Resume Exit_Form_Open

    End Sub
    ################################################## ##########################

    This will enable/disable buttons based on the security level for each form. You can also do the same thing based on the ViewID of a user...with this you can set edit, delete, add properties for each user.

    Once that is done change the splashscreen to something you like, and point it to your main menu. The Main menu in the DB is titled "fmnuMainMenu" If you change the name of your main menu to this, no additional coding will be needed to open it from the splashscreen.

    The Admin menu allows you change/add Access and View levels, reset a users password(PW) and it will automatically e-mail it to them. If you do not have access to E-Mail, you will have to change things around to bypass the e-mail code, and to make sure you know what the new PW is. The new password will be a randomly generated 8 letter password. The next time they log on they will have to use this password and they will be promted for a new one.

    This DB uses a default table titled "tblUsers" for all people in the DB. This is in addition to "tblSecurity" which only lists the security settings for each person. The reason I seperated the two, was to be able to hide the security table, and still have access to the table of people.

    The first time you open the DB login as

    ADMIN
    PASSWORD

    from there you will be promted to change your password. After that you will have full access to add new users, and apply their security settings.

    I hope this can help you out!!

    Any questions, please PM me at UtterAccess!!

    Nick Phillips
    This example is from the UtterAccess.com Access Code Archive forum here UtterAccess Discussion Forums -> Access Code Archive
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  10. #10
    Join Date
    Mar 2009
    Posts
    7
    Thanks for the quick reply!
    I really don't know anything about VBA! This is my first db, and MS really screwed my pooch with this 2007 lack of user accounts! Maybe this will give you a better idea of where I am with this problem...

    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Err_Form_Open


    If User.AccessID = 1 Then
    'Here list all buttons that should be anabled for Admin only

    If this is where I list my buttons, I don't know the correct way of doing this!
    Me.MYBUTTONNAME(cmdAdmin).Enabled = True If this is where I list my buttons, I don't know the correct way of doing that here, either!
    Else
    Me.cmdAdmin.Enabled = False
    End If


    If User.AccessID = >1 Then
    'Here list all buttons that should be enabled for level 2 only
    Me.cmdbutton.Enabled = True
    Else
    Me.cmdbutton.Enabled = False
    End If


    Exit_Form_Open:
    Exit Sub

    Err_Form_Open:
    MsgBox Err.Description
    Me.Visible = True
    Resume Exit_Form_Open

    End Sub

  11. #11
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by gerald_brasher View Post
    Thanks for the quick reply!
    I really don't know anything about VBA! This is my first db, and MS really screwed my pooch with this 2007 lack of user accounts! Maybe this will give you a better idea of where I am with this problem...

    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Err_Form_Open


    If User.AccessID = 1 Then
    'Here list all buttons that should be anabled for Admin only

    If this is where I list my buttons, I don't know the correct way of doing this!
    Me.MYBUTTONNAME(cmdAdmin).Enabled = True If this is where I list my buttons, I don't know the correct way of doing that here, either!
    Else
    Me.cmdAdmin.Enabled = False
    End If


    If User.AccessID = >1 Then
    'Here list all buttons that should be enabled for level 2 only
    Me.cmdbutton.Enabled = True
    Else
    Me.cmdbutton.Enabled = False
    End If


    Exit_Form_Open:
    Exit Sub

    Err_Form_Open:
    MsgBox Err.Description
    Me.Visible = True
    Resume Exit_Form_Open

    End Sub
    So you do not hijack this thread, I would recommend that you use the thread you started

    Over Here

    I replied there
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •