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

    Unanswered: 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

  2. #2
    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

  3. #3
    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
    Note: Access 2007 does support the built in user level security for in the .MDB format.


    I would probably use the form's On Current event.

    In the example they used something like
    Code:
    Me.cmdAdmin.Enabled = False
    The command button's name is cmdAdmin. You will need to replace the cmdAdmin with your actually command button's names

    Code:
    Me.cmd_Button_Name_Goes_Here.Enabled = False

    Hope this helps ...
    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

  4. #4
    Join Date
    Mar 2009
    Posts
    7
    Quote Originally Posted by HiTechCoach View Post
    Note: Access 2007 does support the built in user level security for in the .MDB format.


    I would probably use the form's On Current event.

    In the example they used something like
    Code:
    Me.cmdAdmin.Enabled = False
    The command button's name is cmdAdmin. You will need to replace the cmdAdmin with your actually command button's names

    Code:
    Me.cmd_Button_Name_Goes_Here.Enabled = False

    Hope this helps ...
    This does help, and I do appreciate it. I have tried this already, and I tried it again using this code in the form's On Current event:

    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.EnterManualLinksButton.Enabled = True
    Else
    Me.EnterManualLinksButton.Enabled = False

    Me.EditDeleteFormsButton.Enabled = True
    Else
    Me.EditDeleteFormsButton.Enabled = False

    End If
    Exit_Form_Open:
    Exit Sub

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

    End Sub

    It didn't work for me. If this looks right to you, please let me know! Also, I neglected to post that instead of an actual "button", I am using labels with macros in the On Click event.... could that be the problem? If so, I really would like to keep them if possible....

  5. #5
    Join Date
    May 2010
    Posts
    601
    please post the error message and line that is highlighted.


    there is an error in the VBA Code syntax. The second ELSE is mismatched. There is no IF to pair with it.

    I can't tell if the second ELSE should just be deleted.

    I do not use label for command button. I also never ise a marco to do what VBA can do. In my opinion VBA is superior in many ways. For me , the major thing VBA provides is the error handling required for a stable application.
    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

  6. #6
    Join Date
    Mar 2009
    Posts
    7
    Quote Originally Posted by HiTechCoach View Post
    please post the error message and line that is highlighted.


    there is an error in the VBA Code syntax. The second ELSE is mismatched. There is no IF to pair with it.

    I can't tell if the second ELSE should just be deleted.

    I do not use label for command button. I also never ise a marco to do what VBA can do. In my opinion VBA is superior in many ways. For me , the major thing VBA provides is the error handling required for a stable application.
    MS Visual Basic compile error: Method or data member not found.

    The "Private Sub Form_Open(Cancel As Integer)" line is highlighted as if with a yellow highlighter, with an arrow the left of this line pointing to this line.

    This part ".Enabled =" of the first line that includes this statement "Me.EnterManualLinksButton.Enabled = True" is highlighted as if to be copied.

  7. #7
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by gerald_brasher View Post
    MS Visual Basic compile error: Method or data member not found.

    The "Private Sub Form_Open(Cancel As Integer)" line is highlighted as if with a yellow highlighter, with an arrow the left of this line pointing to this line.

    This part ".Enabled =" of the first line that includes this statement "Me.EnterManualLinksButton.Enabled = True" is highlighted as if to be copied.
    AFAIK, a label does not have an enabled property. You will need to use a command button.
    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

  8. #8
    Join Date
    Mar 2009
    Posts
    7
    Yes, I got it to work with a command button...but only one button at a time....in the original Readme file it looks like I should be able to list more than one button at a time, but once again, I don't know how to do this! Any help you can give me is really appreciated!

  9. #9
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by gerald_brasher View Post
    Yes, I got it to work with a command button...but only one button at a time....in the original Readme file it looks like I should be able to list more than one button at a time, but once again, I don't know how to do this! Any help you can give me is really appreciated!
    See if this examle helps:

    Code:
    If User.AccessID = 1 Then
    'Here list all buttons that should be anabled for Admin only
    
       Me.EnterManualLinksButton.Enabled = True
       Me.EditDeleteFormsButton.Enabled = True
    
       
       Me.cmdButtonForAdmin1.Enabled = True
       Me.cmdButtonForAdmin2.Enabled = True
       Me.cmdButtonForAdmin3.Enabled = True
       Me.cmdButtonForAdmin4.Enabled = True
    
    Else
    
       Me.EnterManualLinksButton.Enabled = False
       Me.EditDeleteFormsButton.Enabled = False
    
       Me.cmdButtonForAdmin1.Enabled = False
       Me.cmdButtonForAdmin2.Enabled = False
       Me.cmdButtonForAdmin3.Enabled = False
       Me.cmdButtonForAdmin4.Enabled = False
    
    End If

    Another way to enable a button only for User.AccessID = 1

    Code:
    Private Sub Form_Current()
    
       Me.EnterManualLinksButton.Enabled = (User.AccessID = 1)
       Me.EditDeleteFormsButton.Enabled = (User.AccessID = 1)
       
       Me.cmdButtonForAdmin1.Enabled = (User.AccessID = 1)
       Me.cmdButtonForAdmin2.Enabled = (User.AccessID = 1)
       Me.cmdButtonForAdmin3.Enabled = (User.AccessID = 1)
       Me.cmdButtonForAdmin4.Enabled = (User.AccessID = 1)
    
    End Sub
    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
    I really appreciate your help on this...I had to change the labels that I want security for, to buttons, but it does work now! I really appreciate the website and the folks that participate in helping! Again, many thanks!

  11. #11
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by gerald_brasher View Post
    I really appreciate your help on this...I had to change the labels that I want security for, to buttons, but it does work now! I really appreciate the website and the folks that participate in helping! Again, many thanks!
    You're welcome!

    Glad we could assist.
    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

Posting Permissions

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